1

Lets assume we have an employee table as below

employee

id (PK) | name | date_of_birth | age | created_at

We have a scenario where we need to add some metadata for each employee and not every employee will have this metadata, so we create a new table as below

employee_metadata

id (PK) | employee_id (PK) | ... | created_at

There is a 1-1 mapping between both the tables

What would be a better design practice for the primary key of employee_metadata table, 1 or 2?

  1. A separate auto-incrementing id

  2. Using employee_id of employee table

Improve this question

1 Answer 1

Reset to default
0

Entia non sunt multiplicanda praeter necessitatem! (Occam's razor).

If all you're doing is splitting the employee table (once and only once for some/all employees), then use the original PRIMARY KEY! Saves on space both on your hard disk and in your brain! The new PK is just extra baggage in this case!

However, one potential caveats have been pointed out by other posters:

  • multiple metadata records to be associated with a single employee

However, if you don't need it now, you're better off to apply the YAGNI ("You aren't gonna need it") principle.

Improve this answer
4
  • Using the PK from employee table makes sense for all CRUD based scenarios. Also thinking if an auto incrementing ID would make sense for any postgres optimisations?
    – Dushyant Sabharwal
    Jan 29 at 10:42
  • Well, compound natural PKs are (potentially much) bigger than a surrogate IDENTITY generated PKs - that's an optimisation in itself - less data dupliction (in the heap and the index). This is especially true if one uses SMALLINT (two bytes) or MEDIUMINT (four bytes) keys - I find there's almost an automatic unthinking reflex to use an INT (8 bytes on 64 bit systems) without analysing if the data really requires 9.2*10^18 keys. The only reason not to use one is the German tank problem - never publically expose your PKs.
    – Vérace
    Jan 29 at 18:12
  • I feel I disagree @Vérace. In the metadata table we might find 10-20-30 entries for each employee. We need a new PK.
    – Francesco Mantovani
    Jan 30 at 7:57
  • 1
    @FrancescoMantovani - I did say If all you're doing is splitting the employee table - it seems fairly clear from the question that it will happen once and once only for some employees - but I've added a clarification to my answer just in case!
    – Vérace
    Jan 30 at 11:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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