Re: Variable list of values in column

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: John Bell (jbellnewsposts_at_hotmail.com)
Date: 02/21/04 

Date: Sat, 21 Feb 2004 20:37:48 GMT

Hi

In general for a relational database, the best way to store data would be in
a normalised form.
Storing a comma separated list is going to mean alot of work when updating
the data
e.g if you have a projects, projectsupplier and projectpart table
Project
{
    ProjectID
    Name
}

ProjectSupplier
{
    ProjectID
    Supplier Name
}

ProjectPart
{
    ProjectID
    Part Name
}

You may extend this to have Part and Supplier tables in which case
ProjectSupplier and ProjectPart will contain the identifiers for these.

If you need to display a comma separated list is is possible to create these
in a store procedure or function, but the most efficient way is to do it in
the front end.

John

"Cameron" <cam_banks@hotmail.com> wrote in message
news:2cc8a69e.0402211154.7796dcf0@posting.google.com...
> I've just started to work with databases for a project that requires
> SQL Server, so I'm learning all of this as I design and develop the
> application. Anyway I need to create a table that has the following
> structure
>
>
> Project
> {
> UniqueID
> Name
> List of suppilers
> List of parts
> }
>
> The two columns "List of suppilers" and "List of parts" are dynamic,
> so for a given project it is desirable to add and remove "suppilers"
> and "parts". It is also desirable to construct searches over projects
> that contain a supplier or part.
>
> After some digging it would appear that the best way to store these
> lists would be comma separated values. So the question I have is how
> do I construct T-SQL searches and if a part or supplier is removed how
> does I ensure the lists in the project table are updated.
>
>
> Cheers
>
> Cameron