Inheritance vs. common (same named and typed) columns?

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开发者_JS百科 of tables are just simple dictionaries (enumerated sets of names) and have no own columns.

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.

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.

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.

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.