Self learner developer since about 2 years, I'm trying to understand deeper how to design a database in the best and most efficient way possible.
Reading about UML design, I've found this:
Do not automatically add “ID numbers” (surrogate keys) or substitute keys to a table until you are sure that:
• There is at least one candidate key (before the surrogate is added),
• the table is a parent in at least one association, and
• there is no candidate key small enough for its values to be copied many times into the child table.
in the section PK size might matter
Until then, I've always did this (I mean using ID) to identify my rows.
So my questions are:
1. Why should we avoid using ID to identify rows?
2. Candidate keys add some weight to our database, and this can huge if we have millions of records, so why should we use these over surrogate keys?
Thank you for your light and advices,