Storing multiple employee IDs in one column of data

Question

Web app is being written in classic ASP with a MSSQL backend. On this particular page, the admin can select 1 or any/all of the employees to assign the project to. I'm trying to figure out a simple way to store the employee IDs of the people assigned to it in one column.

The list of employees is generated from another table and can be dynamic (firing or hiring) so I want the program to be flexible enough to change based on these table changes.

Basically need to know how to assign multiple people to a project that can later be called up on a differen page or from a different query.

Sorry for the n00bish question, but thanks!

Answer 1

Don't store multiple ID's in one column! Create another table with the primary key of your existing table and a single ID that you want to store. You can then insert multiple rows into this new table, creating a 1:m (one to many) relationship. For example, let's look at an order table:

order:
    order_id
    order_date

and I have a product table...

product:
    product_id
    product_name

Now, you could go down the road of adding a column to order that let you list the products in the order, but that would be bad form. What you want instead is something like..

order_item:
    order_item_id
    order_id
    product_id
    quantity
    unit_price

You can then perform a join to get all of the products for a particular order...

select
    product.*

from orders 

inner join order_item on order_item.order_id = order.order_id
inner join product on product.product_id = order_item.product_id

where orders.order_id = 5

Here's an example order_id of 5, and this will get all of the products in that order.

Answer 2

You need to create another table that stores these values such as. So this new table would store one row for each ID, and then link back to the original record with the original records ID.