I'm not sure I quite understand which part of this is causing the confusion.
It should be
possible in every table to define some primary key. If you can't, that probably means the table contains non-unique values and that violates data normalization constraints.
But that doesn't mean you
should define a separate ID field to be used as an ID field. For example, suppose you have a User table with a UserId field. You have a Skill table that has a UserId field to tell you which user owns the record and a "Skill" field that gives one of the person's skills (Database Design, Mathematics, Windows OS, etc.).
The combination of UserId and Skill is unique because you shouldn't list the same skill twice for the same user. UserId/Skill could be a primary key but do you really need it?
Some database systems require a primary key so they can figure out how to update and delete records (so you may need to create that key if the DB requires it) but your program may never need that combination. Instead your program will probably just select all Skill records with a particular UserId so UserId would make a good index. It would also probably make a good foreign key constraint so you cannot create a Skill record unless there is a corresponding User.
It also doesn't hurt too much to make the primary key. It will slow updates, insertions, and deletions slightly and take up a little more space but it probably won't hurt anything. And it will enforce uniqueness (which is a good idea) so you may want to make the UserId/Skill combination a primary key just to cover all the bases.
Quote:
|
Now I try to find natural keys as per your advise, however for these particular tables I cannot find something to qualify.
|
If you can't find a candidate that requires uniqueness, then there's probably something wrong with your design. In general a table should not be allowed to contain two exactly identical records.
Quote:
|
Also if there are circumstances (like this one) when making up a foreign key will actually help (in my mind as beginner).
|
Foreign keys help protect the database from holding invalid data like a Skill record that doesn't correspond to any User. (You could also define a Skills lookup table for the Skill table. It would list the allowed skills that the Skill table could hold. That would also be a foreign key constraint. It would, for example, prevent the user from accidentally entering Debuging as a skill when it should be spelled Debugging.)
Quote:
|
And third, I am wondering if dates (like registration date) can be used as primary keys in association with something else. I know it is a stretch, but it will be educative to understand why datetime stamps does not cut for the primary keys.
|
Dates are usually not used for primary keys for a couple of reasons. One is that on a large database, eventually you will create two new records at roughly the same time and they will try to use the same timestamp. If you record the time to the millisecond, you reduce the chances of a collision but you also save some information that probably isn't very useful. (And some day you will still get5 a collision, just not soon.)
Some companies use part of a timestamp plus other information. For example, they might use the date plus the user's initials. They may also add on a random number to handle collisions when two people with the same initials open accounts on the same day.
All of these methods are basically creating some arbitrary value to represent users. You can invent systems to try to make them unique but most databases already provide auto-number fields that can automatically assign unique numbers to records anyway so why not use that feature instead? It's easy, guaranteed to be unique, and produces a simple number instead of a complicated value that includes date and time information.
If you want to record the date or time when a record was created, you can also save that in a separate field. Then you can store the data you want (perhaps just the date) and not the timestamp to the millisecond. (Of course you can save the timestamp to the millisecond if you want and some databases may be able to do that automatically so you may as well if you want that data available.)
If you do work with dates, you may also want to consider the format in which you save it. For example, you may want to save dates and times in UTC so you get a true time no matter what part of the world you're in. For example, then a record creates at 9:00am in Los Angeles won't look like it was created before a record created at 10:00am in London.
Many database engines automatically saves dates in UTC and you need to figure out how to convert to the format you want to display on the screen so it may not be an issue.
PK and FK
