实体关系 [英] Entity relationship
问题描述
我对此设计有疑问（ er_lp ）。我的疑问是如何创建与复合键的实体之间的多对多关系，其次，在使用日期类型为pk。这里每台机器每天为一个或多个字段上的不同userDepts工作三班。所以为了保持工作和下班时间的机器的记录我使用shift，taskDay和machinePlate作为pks。正如你将从ER图看到的，我在许多地方的链接表中结束了太多的pks。我不犹豫不要在编码阶段遇到麻烦
I have doubt in this design(er_lp). My doubt is in how to create many - to- many relationship with entities with composite keys, Secondly, in using date type as pk. Here each machines work daily for three shifts for different userDepts on one or more fields. So to keep record of working and down hours of machineries I have used shift,taskDay and machinePlate as pks. As you will see from the ER diagram, I ended up with too many pks in the link table in many places. I hesitate not to get in to trouble in coding phase
有更好的方法吗？
谢谢！
Dejene
发布为第二个问题实体关系。材料重新格式化为：
See also extra information posted as a second question Entity Relationship. The material, reformatted, is:
细化：是的，田地指的是土地。我们在不同的位置有几个甘蔗生长场。它[每个字段？]被命名并且有预算。
Elaboration: Yes, 'Field ' is referring to areas of land. We have several cane growing fields at different location. It [each field?] is named and has budget.
用户不是指在机器上工作的个人。他们是部门。我使用'isDone'表来链接userDept和机器。一个机器可以被多个部门使用，并且许多机器可以用于一个userDept。
User is not referring to individual who are working on the machine. They are departments. I used 'isDone' table to link userDept with machine. A machine can be used by several departments and many machines can work for a userDept.
一个特定的机器可以用于给定班次的多个任务。它可以工作2个小时，并可以在另一个字段上启动另一个任务。我们每天有三班，每班8小时！
A particular machine can be used for multiple tasks on a given shift. It can work for say 2 hours and can start another task on another field. We have three shifts per day, each of 8 hrs!
如果我使用自动增量PK，你认为其他键很重要吗？我不喜欢使用它！
If I use Auto increment PK, do you think that other key are important? I don't prefer to use it!
通常，我在表中单独使用自动增量键。我们如何创建涉及自动增量键的关系？
Usually, I use auto increment key alone in a table. How can we create relationship that involves auto increment keys?
感谢您的深思熟虑的评论！
Thank you for thoughtful comment!!
推荐答案
您始终使用第三个表在两个表之间创建多对多关系，列为每个表的主键，所有列的组合是第三个表的主键。对于具有复合主键的表，此规则不会更改。
You always create many-to-many relationships between two tables using a third table, the rows of which contain the columns for the primary key of each table, and the combination of all columns is the primary key of the third table. The rule doesn't change for tables with composite primary keys.
CREATE TABLE Table1(Col11 ..., Col12 ..., Col1N ...,
PRIMARY KEY(Col11, Col12));
CREATE TABLE Table2(Col21 ..., Col22 ..., Col2N ...,
PRIMARY KEY(Col21, Col22));
CREATE TABLE RelationTable
(
Col11 ...,
Col12 ...,
FOREIGN KEY (Col11, Col12) REFERENCES Table1,
Col21 ...,
Col22 ...,
FOREIGN KEY (Col21, Col22) REFERENCES Table2,
PRIMARY KEY (Col11, Col12, Col21, Col22)
);
这很好。它建议你应该尝试和尽可能保持键简单，但绝对没有必要向后弯曲添加自动增量列到引用表，如果他们有一个自然的复合键，使用方便。 OTOH，涉及关系表的连接更难写，如果你使用复合键 - 我想几次关于我是关于如果任何一个复合键涉及多于两列，至少因为它可能表明问题在设计引用的表。
This works fine. It does suggest that you should try and keep keys simple whenever possible, but there is absolutely no need to bend over backwards adding auto-increment columns to the referenced tables if they have a natural composite key that is convenient to use. OTOH, the joins involving the relation table are harder to write if you use a composite keys - I'd think several times about what I'm about if either composite key involved more than two columns, not least because it might indicate problems in the design of the referenced tables.
查看实际的ER图 - 问题中的'er_lp'URL - 'tbl'前缀似乎微不必要;在数据库中存储数据的东西总是表，所以告诉我用前缀是...不必要。名为机器的表似乎被误称;它不会将机器描述为在特定换档时分配给机器的任务。我猜想字段表是指土地的区域，而不是数据库的一部分。你有'IsDone'表（再一次，没有特别命名），标识为特定班次，因此为特定任务在机器上工作的用户。这涉及机器表（其具有3部分主键）和用户表之间的链接。不清楚特定机器是否可用于给定班次的多个任务。不清楚移位号是否在一天中循环，或者每个移位号是否在每天都是唯一的，但假设必须是每天有三个移位，并且移位号和日期需要识别什么时候发生。推测，Shift表可以识别时间和其他这样的信息。
Looking at the actual ER diagram - the 'er_lp' URL in the question - the 'tbl' prefix seems a trifle unnecessary; the things storing data in a database are always tables, so telling me that with the prefix is ... unnecessary. The table called 'Machine' seems to be misnamed; it does not so much describe a machine as the duty allocated to a machine on a particular shift. I'm guessing that the 'Field' table is referring to areas of land, rather than parts of a database. You have the 'IsDone' table (again, not particularly well named) that identifies the user who worked on a machine for a particular shift and hence for a particular task. That involves a link between the Machine table (which has a 3-part primary key) and the User table. It isn't clear whether a particular machine can be used for multiple tasks on a given shift. It isn't clear whether shift numbers cycle around the day or whether each shift number is unique across days, but the presumption must be that there are, say, three shifts per day, and the shift number and date is needed to identify when something occurred. Presumably, the Shift table would identify times and other such information.
Machine上的三部分主键很好 - 但最好有两个唯一标识符。一个是当前的主键组合;另一个是自动分配的号码 - 自动递增，序列，序列或任何...
The three-part primary key on Machine is fine - but it might be better to have two unique identifiers. One would be the current primary key combination; the other would be an automatically assigned number - auto-increment, serial, sequence or whatever...
信息。
我不知道你正在寻找什么。如果机器表应该跟踪给定机器正在使用什么，那么您可能需要做一些更多的数据结构化。考虑到机器可以在单个班次期间用于不同领域的不同任务，您应该考虑一下MachineTasks表，该表将识别操作开始和结束的日期和时间以及操作类型。对于维修操作，您可以将信息存储在描述修理的表中;对于字段中的常规操作，您可能不需要太多的额外信息。
It is not clear to me any more what you are seeking to track. If the 'Machine' table is supposed to track what a given machine was being used for, then you probably need to do some more structuring of the data. Given that a machine can be used for different tasks on different fields during a single shift, you should think, perhaps, in terms of a MachineTasks table which would identify the (date and) time when the operation started and finished and the type of operation. For repair operations, you'd store the information in a table describing repairs; for routine operations in a field, you might not need much extra information. Or maybe that is overkill.
我不清楚是否代表多个部门执行特定任务，或者您是否只是想在一个班次中注意到机器可能被多个部门使用，但每个任务一次只能有一个部门。如果每个任务都是一个单独的部门，那么只需在主MachineTasks表中包含部门信息作为外键字段。
I'm not clear whether particular tasks are performed on behalf of multiple departments, or whether you are simply trying to note that during a single shift a machine might be used by multiple departments, but one department at a time for each task. If each task is for a separate department, then simply include the department info in the main MachineTasks table as a foreign key field.
如果决定自动增加键，您仍然需要维护组合键的唯一性。这是我看到人们用自动增量字段做的最大的错误。它不是那么简单，一个自动递增键的表也必须有一个第二个唯一约束，但它不是太远的标记。
If you decide on an auto-increment key, you still need to maintain the uniqueness of the composite key. This is the biggest mistake I see people making with auto-increment fields. It isn't quite as simple as "a table with an auto-increment key must also have a second unique constraint on it", but it isn't too far off the mark.
使用自动递增键时，需要检索在表中插入记录时分配的值;然后在将其他记录插入其他表时，在外键列中使用该值。
When you use an auto-increment key, you need to retrieve the value assigned when you insert a record into the table; you then use that value in the foreign key columns when you insert other records into the other tables.
您需要阅读数据库设计 - 我不知道什么目前的好书是我十年前以前对我的学习所做的，因此我的书籍不太可能仍然可用。
You need to read up on database design - I'm not sure what the current good books are as I did most of my learning a decade and more ago, and my books are consequently less likely to be available still.
这篇关于实体关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
