Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

Is this good or bad design to add, by default, ID field in every table in a database, even when you don't see currently to usage of this ID (For instance in a MxN table) ?

share|improve this question
    
if you don't have an id column would any other field(s) make up the primary key? –  Thanos Papathanasiou Nov 29 '11 at 14:10
    
I normally add an ID if they probably are used in another table... it would save space although only a little –  zfm Sep 8 '12 at 14:56
add comment

4 Answers

up vote 3 down vote accepted

In many cases, having an artificial ID on every table is very conveniant; in some cases, it's just a pain in the a**.

Generally, I do that on every project I'm working on.

Advantages:

  • It's generally easier to write or generate access code when each and every table can be accessed by a key field called ID, type Integer.

  • Sometimes, natural keys are volatile. For example, in an already very complex warehouse management system, we were faced with the task that product codes change, e.g. what has been product 001234 will be known as 002345 in the future. Unfortunately, that system didn't use artificial IDs, but the product code as the primary key. Naturally, the product code was also a foreign key in dozens of other tables. Therefore, renumbering was really difficult and expensive and couldn't be done during work hours. The next version of the software used artificial IDs, so it was just a simple UPDATE on the product table.

  • Sometimes, natural keys are not unique even if they should be. In my countries, for some reasons, a few SSN have been issued twice.

  • Concetentated keys become cumbersome when the data structure is complex; having a sub-sub-sub-table with a key concetenated of 4 parts is rather painfull to work with.

Disadvantages:

  • By their nature, those IDs have no meaning outside the system, so you need a lot of lookups to translate those IDs to natural keys.

  • Getting the top key from said sub-sub-sub-table requires a big join through the whole hierachy.

  • Merging incoming data with the existing records is more difficult, because again more lookups are needed.

share|improve this answer
1  
Bullet 2: Was this before ON UPDATE CASCADE? –  Izkata Nov 29 '11 at 15:29
1  
@Izkata, I would never permit on update cascade on an enterprise level database as it is very likely to cause performance/blocking/locking problems when large numbers of records are changed. You really don't want to lock up the users while 10,000,000 records are updated. This is why many people believe a volitile key is bad, it causes too much work in the database when it changes. –  HLGEM Nov 29 '11 at 15:33
    
@HLGEM While I admit to not having had a database such as that to play around with, it seems to me that InnoDB's row-level locking would help mitigate it, especially if pauses were added between updating every X records. Besides, the way ammoQ described it, the real issue was that they'd have to manually change all the foreign keys (The MyISAM engine on MySQL, for example, doesn't support it). –  Izkata Nov 29 '11 at 15:52
    
Izkata: The problem was not issuing 30 update statements instead of one. That part is easy, even without using cascading updates. The real problem was affecting many thousands of rows instead of one, causing deadlocks all over the (admittedly not too well designed) system. Eventually, there was a nightly job to do that. –  user281377 Nov 29 '11 at 22:50
add comment

synthetic keys vs natural keys and single keys vs compound keys are both hotly debated topics that have good positives and negatives on both sides. They are like the tabs vs spaces and curly brackets on their own line debates.

The most important thing is to pick a side and stick with it for consistency throughout the database. generally synthetic keys are used simply because it's hard to have a good natural key for every table and its much easier to be consistent when every table has a synthetic key.

share|improve this answer
add comment

Every table should have a PK,a an Unique identifier, or and ID as you are calling it. In the Case of MxN you have PK, (a Compound one). So there is no need to have another.

Also See: One or Two Primary Keys in Many-to-Many Table?

My Personal Preference pertaining to a related topic: PKs should not have another use aside from being a PK. So I would not use data a a PK, Even if the data is "Naturally unique" IE: SSN, Date, Zip Code Ect.

share|improve this answer
1  
You contradict yourself. The columns of compound key in relation table do have another use aside from being a PK. They are foreign keys too. (Besides, I don't agree. If you have something, that is naturally PK, I'd just go with it. If I didn't have constraint on it, I'd surely end up with conflicting entries one day and if the constraint is there, why not use it as primary key.) –  Jan Hudec Nov 29 '11 at 14:29
2  
Generally Pks are used as FKs in different tables that is part of being a PK, I see no contradiction. –  Morons Nov 29 '11 at 14:34
1  
I do understand your position regarding Natural keys, that is way I was careful to state this is my personal preference. –  Morons Nov 29 '11 at 14:36
2  
@JanHudec: Things that are naturally unique tend to naturally change. Also, I've seen quite often that natural keys tend to be non-integers, like strings, which leads to performance problems if used as PK> –  Goran Jovic Nov 29 '11 at 15:33
    
You also run into issues where you have to deal with a Typos the in PKs. It used to make me crazy. –  Morons Nov 29 '11 at 16:03
add comment

From design point of view, go with the natural, compound primary key and don't invent a synthetic one. Anything that does not have to be there only makes the schema more complicated to understand.

From performance point of view in many databases it's faster to access table by integer primary key than by any other indexed column. These databases will also implicitly add such synthetic key to any table that does not have one (usually called "rowid" or "oid" or similar), so adding it explicitly will cost nothing at all.

For MxN (relation) tables it does not make sense to access them by primary key; you access them by either component and get the set of related entities from the other side. So for these tables it makes no sense to add synthetic primary key.

For other tables with good composed or non-integer primary key I would still not add synthetic one initially, but would consider adding it during performance tuning for tables accessed in performance critical tables.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.