I have a few database tables that really only require a unique id that references another table e.g.
我有一些数据库表,实际上只需要一个引用另一个表的唯一ID,例如
Customer Holiday
******** *******
ID (PK) ---> CustomerID (PK)
Forename From
Surname To
....
These tables such as Holiday, only really exist to hold information regarding a Customer. Therefore, do I need to specify a separate field to hold the ID for the holiday? i.e.
这些表格如Holiday,仅用于保存有关客户的信息。因此,我是否需要指定一个单独的字段来保存假期的ID?即
Holiday
*******
ID (PK)
CustomerID (FK)
...
Or would I be ok, in this instance, to just set the CustomerID as the primary key in the table?
或者,在这种情况下,我可以将CustomerID设置为表中的主键吗?
Regards, James.
5 个解决方案
#1
3
This really depends on what you are doing.
这实际上取决于你在做什么。
if each customer can have only 1 holiday, then yes, you could make the customerid the primary key.
如果每个客户只有一个假期,那么是的,您可以将customerid作为主键。
If each customer can have multiple holidays, then no, you would want to add a new id column, make it the primary. This allows you to select holidays by each customer AND to select individual records by their unique id.
如果每个客户可以有多个假期,那么不需要,您可以添加新的id列,使其成为主要的。这允许您为每个客户选择假期并按其唯一ID选择单个记录。
Additionally if each customer can only have 1 holiday, I'd just add the holiday information to the table, as a one-to-one relationship is typically un-necessary.
此外,如果每个客户只能有1个假期,我只需将假日信息添加到表中,因为一对一的关系通常是不必要的。
#2
0
If I understand your question correctly, you could only use the Customer table as a primary key in Holiday if there will never be any other holiday for that customer in the table. In other words, two holidays for one customer breaks using the Customer id as a primary key.
如果我正确理解了您的问题,那么您只能在Holiday中使用Customer表作为主键,如果该表中的该客户永远不会有任何其他假期。换句话说,一个客户的两个假期使用客户ID作为主键。
#3
0
If there will ever be an object-oriented program associated with this database, each entity (each row) must have a unique key.
如果将存在与此数据库关联的面向对象程序,则每个实体(每行)必须具有唯一键。
Your second design assures that each instance of Holiday can be uniquely identified and processed by an OO application using a simple Object-Relational Mapping.
您的第二个设计确保使用简单的对象关系映射,OO应用程序可以唯一地标识和处理Holiday的每个实例。
Generally, it's best to assure that every entity in the database has a unique, immutable, system-assigned ("surrogate") key. Other "natural" keys can have unique indexes, constraints, etc., to fit the business logic.
通常,最好确保数据库中的每个实体都具有唯一的,不可变的,系统分配的(“代理”)密钥。其他“自然”键可以具有唯一索引,约束等,以适应业务逻辑。
#4
0
Previous answer correct, but also remember, you could have 2 seperate primary keys in each table, and the "holiday" table would have the foreign key to CustomerId.
以前的答案是正确的,但请记住,每个表中可以有2个单独的主键,而“holiday”表将具有CustomerId的外键。
Then you could manage the assignment of holidays to customers in your code, to make sure that only one holiday can be assigned to a customer, but this brings in the problem concurrency, being 2 people adding a holiday to a customer at the same time will most probably result in a customer having 2 holidays.
然后,您可以在代码中管理客户的假期分配,以确保只能为客户分配一个假期,但这会带来问题的并发性,即2个人同时为客户添加假期最有可能导致客户有2个假期。
You could even place holiday fields in the customer table if a customer can only be created with a holiday, but this design is messy, and not really advised
如果客户只能在假期中创建,您甚至可以将假日字段放在客户表中,但这种设计很混乱,并没有真正建议
So once again, option in your question 2 still the best way to go, just giving you your options.
再一次,问题2中的选项仍然是最好的方式,只是给你你的选择。
#5
0
In practice I've found that every table should have a unique primary key identifying the records in those tables. All relationships with other tables should be explicitly declared.
在实践中,我发现每个表都应该有一个唯一的主键来标识这些表中的记录。应明确声明与其他表的所有关系。
This helps others understand the relationships better, especially if they use a tool to reverse-engineer the schema into a visual representation.
这有助于其他人更好地理解关系,特别是如果他们使用工具将模式反向工程为可视化表示。
In addition, it gives you more flexibility to expand your solution in the future. You may only have one holiday per customer now, but this is much more difficult to change if you make customer ID the primary key.
此外,它还为您提供了更大的灵活性,可以在未来扩展您的解决方案。您现在每个客户可能只有一个假期,但如果您将客户ID作为主键,则更难以更改。
If you want to mandate the uniqueness of customer in the holiday table, create a unique index on that foreign key. In fact, this could improve performance when querying on customer ID (although I'm guessing you won't see enough records to notice this improvement).
如果要在假期表中强制要求客户的唯一性,请在该外键上创建唯一索引。实际上,这可以在查询客户ID时提高性能(尽管我猜你不会看到足够的记录来注意这一改进)。
#1
3
This really depends on what you are doing.
这实际上取决于你在做什么。
if each customer can have only 1 holiday, then yes, you could make the customerid the primary key.
如果每个客户只有一个假期,那么是的,您可以将customerid作为主键。
If each customer can have multiple holidays, then no, you would want to add a new id column, make it the primary. This allows you to select holidays by each customer AND to select individual records by their unique id.
如果每个客户可以有多个假期,那么不需要,您可以添加新的id列,使其成为主要的。这允许您为每个客户选择假期并按其唯一ID选择单个记录。
Additionally if each customer can only have 1 holiday, I'd just add the holiday information to the table, as a one-to-one relationship is typically un-necessary.
此外,如果每个客户只能有1个假期,我只需将假日信息添加到表中,因为一对一的关系通常是不必要的。
#2
0
If I understand your question correctly, you could only use the Customer table as a primary key in Holiday if there will never be any other holiday for that customer in the table. In other words, two holidays for one customer breaks using the Customer id as a primary key.
如果我正确理解了您的问题,那么您只能在Holiday中使用Customer表作为主键,如果该表中的该客户永远不会有任何其他假期。换句话说,一个客户的两个假期使用客户ID作为主键。
#3
0
If there will ever be an object-oriented program associated with this database, each entity (each row) must have a unique key.
如果将存在与此数据库关联的面向对象程序,则每个实体(每行)必须具有唯一键。
Your second design assures that each instance of Holiday can be uniquely identified and processed by an OO application using a simple Object-Relational Mapping.
您的第二个设计确保使用简单的对象关系映射,OO应用程序可以唯一地标识和处理Holiday的每个实例。
Generally, it's best to assure that every entity in the database has a unique, immutable, system-assigned ("surrogate") key. Other "natural" keys can have unique indexes, constraints, etc., to fit the business logic.
通常,最好确保数据库中的每个实体都具有唯一的,不可变的,系统分配的(“代理”)密钥。其他“自然”键可以具有唯一索引,约束等,以适应业务逻辑。
#4
0
Previous answer correct, but also remember, you could have 2 seperate primary keys in each table, and the "holiday" table would have the foreign key to CustomerId.
以前的答案是正确的,但请记住,每个表中可以有2个单独的主键,而“holiday”表将具有CustomerId的外键。
Then you could manage the assignment of holidays to customers in your code, to make sure that only one holiday can be assigned to a customer, but this brings in the problem concurrency, being 2 people adding a holiday to a customer at the same time will most probably result in a customer having 2 holidays.
然后,您可以在代码中管理客户的假期分配,以确保只能为客户分配一个假期,但这会带来问题的并发性,即2个人同时为客户添加假期最有可能导致客户有2个假期。
You could even place holiday fields in the customer table if a customer can only be created with a holiday, but this design is messy, and not really advised
如果客户只能在假期中创建,您甚至可以将假日字段放在客户表中,但这种设计很混乱,并没有真正建议
So once again, option in your question 2 still the best way to go, just giving you your options.
再一次,问题2中的选项仍然是最好的方式,只是给你你的选择。
#5
0
In practice I've found that every table should have a unique primary key identifying the records in those tables. All relationships with other tables should be explicitly declared.
在实践中,我发现每个表都应该有一个唯一的主键来标识这些表中的记录。应明确声明与其他表的所有关系。
This helps others understand the relationships better, especially if they use a tool to reverse-engineer the schema into a visual representation.
这有助于其他人更好地理解关系,特别是如果他们使用工具将模式反向工程为可视化表示。
In addition, it gives you more flexibility to expand your solution in the future. You may only have one holiday per customer now, but this is much more difficult to change if you make customer ID the primary key.
此外,它还为您提供了更大的灵活性,可以在未来扩展您的解决方案。您现在每个客户可能只有一个假期,但如果您将客户ID作为主键,则更难以更改。
If you want to mandate the uniqueness of customer in the holiday table, create a unique index on that foreign key. In fact, this could improve performance when querying on customer ID (although I'm guessing you won't see enough records to notice this improvement).
如果要在假期表中强制要求客户的唯一性,请在该外键上创建唯一索引。实际上,这可以在查询客户ID时提高性能(尽管我猜你不会看到足够的记录来注意这一改进)。