在一列数据中存储多个员工ID

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.

Web应用程序是用经典ASP编写的,带有MSSQL后端。在此特定页面上,管理员可以选择1个或任何/所有员工来分配项目。我试图找出一种简单的方法来将分配给它的人员的员工ID存储在一列中。

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!

对不起n00bish的问题,谢谢!

2 个解决方案

#1

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.

您需要创建另一个存储这些值的表,例如。因此,这个新表将为每个ID存储一行,然后使用原始记录ID链接回原始记录。

#2

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:

不要在一列中存储多个ID!使用现有表的主键和要存储的单个ID创建另一个表。然后,您可以在此新表中插入多行,从而创建1:m(一对多)关系。例如,让我们看一下订单表:

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.

这是一个示例order_id为5,这将获得该订单中的所有产品。

#1