O/R Mapping for fun and profit

Posted on by

Back on this old chestnut again…

  • Always use a synthetic primary key.
  • Never use a primary key with business meaning (in case my last point wasn’t clear).
  • Call your primary key something that can’t be mistaken for something with business meaning. ID is usually good. PK works too.
  • Don’t include the table name in any of your column names. They already know which table they belong to.
  • Use Longs as primary keys, and generate them from a sequence.
  • Use unique and ‘NOT NULL’ constraints to identify secondary keys (the ones that DO have business meaning).
  • Use hibernate.

O/R mapping is a compromise. Bending both your DB and your object layer slightly to acknowledge this fact is far better than bending one of them over backwards to avoid changing the other. Using synthetic primary keys is almost always a good move anyway, and helps object persistence a lot. Having a consistent primary key name and datatype makes automated persistence much easier (ie. code-generation).

7 thoughts on “O/R Mapping for fun and profit”

  1. Dude, if the average programmer just followed HALF of that, the world would be a better place. I HATE “meaningful” primary keys. I hate DB schemas that are geared towards making it easy to do queries in SQL*Plus.

    Even for to-many relationships, I prefer the synthetic key vs. the “sequence number + foreign key == primary key” business.

  4. Exactly; good mini-bible for the O/R process.

    I’m with Dave too, I use primary keys even in associations, rather than deal with pk+fk=pk2.

    One nice thing with O/R mapping is it only takes a little extra class construction to avoid the problems of ‘bending’ your object layer to meet your ‘O/R’ tools expectations. Interfaces and object implementations for the mapping layer do wonders. Book is an interface, and BookImpl and BookHibImpl are two implementations, one which meets the needs of hibernate, one that may not for instance.

  5. I’ve used String uuid’s as a primary keys with great success. You don’t have to worry about duplicate keys across types of objects, which gives you the ability to create structures in the application that index by id, etc.

  6. Have you done any performance profiling to compare String UUIDs vs Long sequence numbers?

    I have always been a bit concerned that for very large tables, the db can’t index the VARCHAR columns as efficiently.

    However, I notice that in the MS world, it is recommended to use GUID (SQL Server has a special column type for that). Its certainly good in the case of multiple databases on different sites!

  7. Key Point missing.
    1. Buy a book and learn Relational theory.
    The number of OO gurus who have no clue about relational databases is simply amazing.

Comments are closed.