Best Practices on Primary Key Database Modelling


Performance and scalability: Best Practices on Primary Key Database Modelling

  1. Hello !! I was recently asked about how could be a "best practice" on primary key's database modelling and using it with Hibernate or another relational mapping technology. It comes that I have essentially, 2 ways: 1) Using an Attribute ID and making it fed by a sequence or auto-numeration and: 2) Using Natural Keys (VARCHAR columns) and/or multiple columns for relationship with entities. The theory of OO says that an object should generate his own ID, so the first fits in that. I ALWAYS recommend the first one, because I can´t see any advantages using the second one that isn´t solved by the first way. So, what is the best: Using a sequence strategy or a natural/composite key strategy ? I really want to know your point of view. Please, comment this thread. Sincerely, George Gastaldi
  2. Most database engines do not do extremely well with VARCHAR keys, and sequential numeric keys are a better idea if the nature of keyed columns can change as a project moves forward (the cases that you expect your key column be always unique but for some strange requirement it turns out to be a false assumption). On the other hand, many databases pick one of unique keys as how physically data should be stored on disk, and primary keys are a good candidate for this. Therefore, you may want to use the second option if your data is well keyed on its columns. This will improve query times drastically when your application is under pressure. Some databases let you choose this order (M$ SQLServer has clustered indexes).