The rules changed while I wan't looking?

heartburn · **Joined:** Mon Oct 11, 2004 11:58 pm **Posts:** 3

I'm very new to Hibernate. In fact I haven't even had a chance to try it yet. But I've been reading Hibernate in Action, and I've been lurking around the forums, and suddenly it seems that everything I thought I knew about database design has turned out to be wrong.

For instance, why are composite keys bad? Why does the book keep using the dirty word "legacy" when referring to them? Why is table-per-class-hierarchy recommended over table-per-subclass? Consider the following structure (only keys are shown):

Code:

create table Party (
  party_id int not null primary key
);

create table Person (
  party_id int not null primary key references Party(party_id)
);

create table Organization (
  party_id int not null primary key references Party(party_id)
);

create table RoleType (
  role_type_id int not null primary key
);

create table RelationshipType (
  from_role_type_id int not null references RoleType(role_type_id),
  to_role_type_id int not null references RoleType(role_type_id),
  primary key (from_role_type_id, to_role_type_id)
);

create table Relationship (
  from_party_id int not null references Party(party_id),
  to_party_id int not null references Party(party_id),
  from_role_type_id int not null,
  to_role_type_id int not null,
  primary key (from_party_id, to_party_id, from_role_type_id, to_role_type_id),
  constraint fk_Relationship_RelationshipType 
    foreign key(from_role_type_id, to_role_type_id) references
    RelationshipType(from_role_type_id, to_role_type_id)
);

This ddl is a common idiom for a database that contains information about people and organizations. It is a data modeling pattern in the same sense that Facade & Flyweight are object modeling patterns. If I were to say "party structure" to an experienced data modeler, I would expect the image of a structure similar to the one above to appear in his/her head. Yet this structure makes heavy use of compound keys, and it implies a table per subclass class model.

I could certainly introduce new surrogate keys in place of the compound keys, but I would have to complicate the design with unique indexes to maintain data integrity. And I could flatten the Party-Person-Organization thing, but my boss would probably send me back to Database Normalization 101 (if I was lucky).

I like the whole idea of Hibernate and ORMs in general. And I understand that Hibernate can work with the structure above, and I can't wait to try it out. But I don't understand why the book seems to recommend compromising the data model to save the code.

Isn't data still much more valuable than code? Twenty years from now which is more likely to still have any value: your java program, or your data?

heartburn · **Joined:** Mon Oct 11, 2004 11:58 pm **Posts:** 3

I ran the ddl above in PostgreSQL and reverse-engineered it with Visio. Here's the jpeg:

christian · **Posted:** Sat Oct 16, 2004 3:43 am

Quote:

For instance, why are composite keys bad? Why does the book keep using the dirty word "legacy" when referring to them? Why is table-per-class-hierarchy recommended over table-per-subclass?

I suggest you read again, because that is certainly not the case. Why are composite keys bad? Because they often imply a natural key as well.

heartburn · **Joined:** Mon Oct 11, 2004 11:58 pm **Posts:** 3

Thanks for replying Christian.

So you'd agree that this structure is sound?

christian · **Posted:** Sat Oct 16, 2004 10:50 am

Sure, as long as you don't trust unstable natural keys, composite keys only mean "more work" in certain situations. Why? Because you have to make sure that the correct keys are assigned yourself and can't use some automatic generator. As long as you are aware of this, there isn't a problem with composite keys from a modeling perspective.