Hibernate and surrogate keys
I had a relatively new developer stop by my desk a few minutes ago asking my opinions the use of surrogate keys vs. using natural keys while doing physical database design. The reason he asked is that Hibernate (the "free" object/relational mapping tool of choice these days) wants you to have a surrogate key on every table.
My two cents are as follows:
- if no natural key exists, well, obviously create a surrogate key
- if a natural key exists and it is only one column, use it if at all possible
- if a natural key exists and it is a composite key of two columns, let's try to use the natural key if it's not too difficult
- if a natural key exists and it is a composite key of more then two columns, let's use a surrogate key and create a unique key on the natural key
When creating surrogate keys, I follow these naming conventions and guidelines:
- if the object (table) is called something like user, I name the surrogate key column USER_SEQ.
- I then create an Oracle sequence in my schema by the same name, hence I always know the name of the Oracle sequence that is used for this object (table).
Now I know what to call my surrogate key when I begin my physical data model (after my conceptual/logical model is complete). I also know what to call my sequence and I know that my column name will be the same throughout my entire database (instead of just calling it ID in the table and then random names on all joining tables). All too often I see tables with a surrogate key named ID and then that column is named something different in children tables.
It is important that columns be named consistently throughout your database.
Stated differently: A column name means one and only one thing throughout the entire database.
Stated differently (again): An single common attribute has the SAME NAME throughout all the tables.
Sounds silly, but when these simple rules are followed, you not only get a consistent and clean design, you can also development at a much quicker rate (since you know your ground rules in advance).
Until next time...Rich
My two cents are as follows:
- if no natural key exists, well, obviously create a surrogate key
- if a natural key exists and it is only one column, use it if at all possible
- if a natural key exists and it is a composite key of two columns, let's try to use the natural key if it's not too difficult
- if a natural key exists and it is a composite key of more then two columns, let's use a surrogate key and create a unique key on the natural key
When creating surrogate keys, I follow these naming conventions and guidelines:
- if the object (table) is called something like user, I name the surrogate key column USER_SEQ.
- I then create an Oracle sequence in my schema by the same name, hence I always know the name of the Oracle sequence that is used for this object (table).
Now I know what to call my surrogate key when I begin my physical data model (after my conceptual/logical model is complete). I also know what to call my sequence and I know that my column name will be the same throughout my entire database (instead of just calling it ID in the table and then random names on all joining tables). All too often I see tables with a surrogate key named ID and then that column is named something different in children tables.
It is important that columns be named consistently throughout your database.
Stated differently: A column name means one and only one thing throughout the entire database.
Stated differently (again): An single common attribute has the SAME NAME throughout all the tables.
Sounds silly, but when these simple rules are followed, you not only get a consistent and clean design, you can also development at a much quicker rate (since you know your ground rules in advance).
Until next time...Rich
Comments