Any resources for common data model design patterns?

Question

I'm looking for a resource that shows common design patterns or best practices for data models. For instance, a [very simplified] course model might have:

Student has many Enrollments

Course has many Sections

Sections has many Enrollments

There seem to be many approaches to setting up data models and relationships, and some are more obvious than others. Often, I find myself thinking about it one way and finding out much later that there is a quirk or complication I missed.

For more complex systems, like ERP, Reservations, eCommerce, etc., there must be some standards and best practices but I can't find them anywhere. Any resource suggestions would be welcome. I've googled around quite a bit, but I didn't see any that made sense.

Answer 1

This is a copy of my answer on DBA.

David Hay's

• Enterprise Model Patterns. This is a beast of a book, but has some great patterns.
• Conventions of Thought. More stuff on MRP.
• A Meta-Data Map . Haven't read this one.

Len Silverston's

• Data Model Resource Book Vol. 1. Your main data model patterns.
• Data Model Resource Book Vol. 2. Case studies by industry.
• Data Model Resource Book Vol. 3. A deeper explanation of Vol 1.

Michael Blaha's Patterns of Data Modeling. This one has some interesting temporal, graph, and tree models.

Martin Fowler's Analysis Patterns. This one skims some of the other patterns, but gives accounting a solid treatment.

They are all well-rated, and I have read all but one, and they are all very good. Several of them are available on safaribooksonline.

Also, OASIS's Universal Business Language, schemas

Answer 2

Data modeling can be done in varied different ways and your choice of modeling technique will dictate the best practices that you would want to follow. Two common modeling techniques are -

• Entity-relationship Modeling, and
• Dimensional Data Modeling

The scenario that you mentioned above can very well be modeled as ER model or Dimensional Model. But when you think about best practices, it boils down to the fact whether or not you understand the principle tenets behind those modeling technique.

For example, as long as you model this data in a normalized (3NF etc.) form and able to avoid any update/delete anomaly, reduce data redundancy etc. you can be pretty sure that you have actually followed the best practices required for ER modeling.

Similarly, if you have modeled it in Dimensional modeling, then you would want to ensure that you maintain a proper balance between speed of data extraction versus speed or data insertion / update etc.

As for some resources on design pattern, you may check few of these articles:

• Common Mistakes in Data Modeling
• Other data Modeling articles

Hope this helps.