What model of these is better? A or B? Considering I have more common columns (5 actually for now), many more entities (over 60) and many more relations, while most of tables are just simple dictionaries (enumerated sets of names) and have no own columns.
+2
A:
I would definitely go with example A. All your queries are going to be much more complicated (and mostly likely much slower) having to join the NamedEntity for every lookup.
Even if your ORM completely abstracts this inheritance relationship away from you, I would bet that you're going to have to write a raw SQL query at some point, and having to join NamedEntity onto every table will be a huge pain.
Andy White
2010-05-08 22:45:43
But I have more than 60 entities having 5 fields in common (not mentioning Id). And "don't repeat yourself" principle makes me nervous to I copy-paste each of 5 fields 60 times.
Ivan
2010-05-08 22:54:14
I'm not sure what your common columns are, but as far as the Id, and other "common" columns like CreateDate, LastModifiedDate, I would definitely put these on each table. You're right, there is repetition there, but trust me, don't get too fancy with data inheritance relationships. If you have over 60 entities that share the same domain-related columns (i.e. non-auditing columns), it might be worth considering a different inheritance model in your database, like "table-per-hierarchy."
Andy White
2010-05-08 23:02:47
A:
I agree with Andy. There is no point trying to abstract that like that and mixing things from different domains (customer name, city name, country name) in one column. It would make choosing data types, integrity constraints, referential constraints and Querying all needlessly complex and inefficient.
Martin Smith
2010-05-08 22:51:44
+1
A:
I would place those shared fields in a ComplexType and then add a property of that type to each entity.
(Inheritance when using ORM should obey the same rules as in normal OO, when you have an "is a" relationship.)
So encapsulate the repitition in a complex type and avoid the inheritance.
Although, Name should probably not go in that complex type, it seems like a core property of the entity.
Roger Alsing
2010-05-09 06:54:08
A:
There is a general pattern called "Generalization-Specialization". OO deals with gen-spec by means of inheritance. This seems simple and natural to the OO mindset.
SQL and the relational data model that's behind SQL are quite powerful vehicles for structuring reality. But the mindset is quite different from OO. There is a way to design tables for the gen-spec pattern, and it's quite useful and powerful. It's not quite as simple as inheritance, and it's often glossed over in introductory database design tutorials.
If you will search the web for "generailization specialization relational modeling", you will find at least a dozen good articles, and some not so good ones, for designing tables to model the gen-spec pattern.
Walter Mitty
2010-05-09 13:35:50