How do I properly design a database? Foreign Keys vs Secondary Keys?

Question

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...?

Answer 1

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 ;) ...

Answer 2

Your question cannot be answered in one simple SO post. There are a lot of things to consider when creating a database. The best thing I ever did to learn about databases and how to create them was to read a book called "Database Design For Mere Mortals" written by Michael Hernandez.

See my post on Programmers to the question How do you approach database design?