如何正确设计数据库？外键与次要键？ [英] How do I properly design a database? Foreign Keys vs Secondary Keys?
问题描述
我有点停留在外键和辅助键上。什么时候使用辅助键和外键？
tbl_Users 1：* tbl_Item //关系
tbl_Item 1：1 tbl_ItemDetail1& tbl_ItemDetail2 //关系
tbl_Item 1：N tbl_ItemDetail3 //关系
tbl_Users
-UserID - PK
tbl_Item
-ItemID - PK
-UserID - FK
tbl_ItemDetail1
-ItemDetail1ID - PK //如果我有ItemID，我甚至需要这个吗？与
--ItemID - FK
-Count
-Duration
-Frequency
tbl_ItemDetail2
-ItemDetail2ID - PK //如果我有ItemID，我甚至需要这个吗？与
--ItemID - FK
-OnOff
$ b $之间的1：1关系b-Temperature
-Voltage
tbl_ItemDetail3
-ItemDetail3ID - PK //具有1：N关系
-ItemID - FK
-Contrived Value1
-Contrived Valu2
编辑： / p>
感谢您的回复，我已更新我的原始帖子以正确反映我的数据库。
在数据库中我正在创建，项目有〜9项细节。每个项目的详细信息是5-15列数据。
拥有一个100列的表格没有意义吗？
数据库执行3种声明完整性：
- >域 - 字段的类型和CHECK约束。
- 密钥的完整性 - PRIMARY KEY或UNIQUE约束。
- 参考完整性 - FOREIGN KEY。
密钥唯一标识行在桌子里所有密钥在逻辑上是等同的，但由于实际原因，其中一个被选为主，其余的被认为是备用（有一些涉及NULL的并发症，但是我们不在这里）
$ b另一方面，FOREIGN KEY作为一种从一个表到另一个表的指针，DBMS本身保证这个指针永远不会其中一个FOREIGN KEY）。1：N details当然，这是另一回事，并且通过单独的表自然地建模。
既然你说细节1和细节2 是1：（0 ..）1和detail 3是1：N，你的更新数据模型可能看起来像这样：
BTW，上述模型使用<强>识别关系，导致更多的自然键。 非识别关系/替代键方法将如下所示：
每种方法都有其优点，但这篇文章已经变得有点长了）
here are some generic tables, I am trying to fully understand how to properly setup databases tables. Are these setup correctly? I want to be able to lookup a user's Items and Item Details as fast as possible. FYI for this example ItemDetailsX do not share the same data fields.
I am a little bit stuck on Foreign Keys and Secondary keys. When do you use a Secondary Key vs a Foreign Key?
tbl_Users 1:* tbl_Item //relationship
tbl_Item 1:1 tbl_ItemDetail1 & tbl_ItemDetail2 // relationship
tbl_Item 1:N tbl_ItemDetail3 //releationship
tbl_Users
-UserID - PK
tbl_Item
-ItemID - PK
-UserID - FK
tbl_ItemDetail1
-ItemDetail1ID - PK //Do I even need this if I have ItemID? Its a 1:1 relationship with
-ItemID - FK
-Count
-Duration
-Frequency
tbl_ItemDetail2
-ItemDetail2ID - PK //Do I even need this if I have ItemID? Its a 1:1 relationship with
-ItemID - FK
-OnOff
-Temperature
-Voltage
tbl_ItemDetail3
-ItemDetail3ID - PK //Has a 1:N relationship
-ItemID - FK
-Contrived Value1
-Contrived Valu2
EDIT:
Thanks for the replies, I have updated my original post to properly reflect my database.
In the database that I am creating, the Item has ~9 item details. Each item details is 5-15 columns of data.
Having 1 table with like 100 columns does not make sense...?
Databases enforce 3 kinds of declarative integrity:
- Integrity of domain - field's type and CHECK constraint.
- Integrity of key - PRIMARY KEY or UNIQUE constraint.
- Referential integrity - FOREIGN KEY.
A key uniquely identifies rows in the table. All keys are logically equivalent, but for practical reasons one of them is chosen as "primary" and the rest are considered "alternate" (there are some complications involving NULLs, but let's not get into that here).
On the other hand, a FOREIGN KEY is as a kind of "pointer" from one table to another, where the DBMS itself guarantees this "pointer" can never "dangle". The foreign key references the (primary or alternate) key in "parent" table, but the "child" endpoint does not need to be a key itself (and usually isn't).
- When a row is modified or deleted from the parent table, this change is either cascaded to the child table (ON [UPDATE/DELETE] [CASCADE/SET NULL/SET DEFAULT]) or the whole operation is blocked (ON [UPDATE/DELETE] RESTRICT).
- If a child is inserted or modified, it is checked against the parent table to make sure this new value exists there.
The constraints change the meaning of data. Indexes, on the other hand, do not change the meaning of data - they are here purely for performance reasons. Some databases will even allow you to have a key without an underlying index, although this is usually a bad idea performance-wise. An index underneath the primary key is called "primary index" and all other indexes are "secondary".
BTW, there is "secondary index" and there is "alternate key", but there is no such thing as "secondary key".
I'm not quite sure what is your design goal, but I'm guessing something like this would be a decent starting point:
I see no purpose in extracting details to separate tables if they are always in 1:1 relationship with the item.
--- EDIT ---
Some questions you'll need to ask yourself before being able to arrive at optimal database design:
Is there a real 1:1 relationship between item and detail or is it actually 1:0..1 (i.e. some details are optional?).
- If 1:1, just using columns is the most natural representation. BTW, a decent DBMS will have no trouble handling 100 columns.
- If 1:0..1, you'll have to decide whether to use NULL-able columns, or separate tables. Just keep in mind that most DBMSes are really efficient in storing NULLs (typically just a small bitmap per row), so separating the data to a different table might not get you much, and in fact may substantially worsen the querying performance due increased need for JOINing.
Are all detail kinds predetermined (i.e. can you confidently say you won't need to add any new kinds of details later in the application's lifecycle)?
- If yes, just use columns.
- If no, adding columns on the large existing database can be expensive - whether it is expensive enough to warrant using separate table is up to you to measure.
You could also consider generalizing all the details as name/value pairs and representing them within a single 1:N table (not shown here). This is very flexible and "evolvable", but has its own set of problems.
How do you intend to query the data? This is a biggie and may influence substantially whether to go with "columns" or "separate table" approach, indexing etc...
BTW, the 1:0..1 with separate tables can be modeled like this...
...and 1:1 can be modeled like this...
...but this introduces circular dependency that must be handled in a special way (usually by deferring one of the FOREIGN KEYs).
1:N details, of course, are another matter and are naturally modeled through separate tables.
Since you say "detail 1" and "detail 2" are 1:(0..)1 and "detail 3" is 1:N, your "updated" data model would probably look something like this:
BTW, the above model uses identifying relationships which result in more "natural" keys. Non-identifying relationships / surrogate keys approach would look like this:
Each approach has its advantages, but this post is becoming a little long already ;) ...
这篇关于如何正确设计数据库？外键与次要键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
