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) ?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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.
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.
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.
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.
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.