One thing I have struggled with when I have created Operational Data Stores is the tendency to create generic tables that promote re-use. I find these are usually tables like Address and Person. In an enterprise environment there may be many applications or Systems of Record that store Person or Address information. There is the tendency when we create the objects in an Operational Data Store to store like information in one table.
In most cases having multiple Address or Person tables don’t violate any rules of Normalization. It is just a habit of organization to try to have only one table for a certain entity. We probably don’t have a requirement to report on all addresses together? Maybe for Person, but not for Address.
So the question is, Is this over-modeling?
Early in my modeling career I would have thought the question ridiculous. But now, I’m not so sure. If I had it to do over, I think I would duplicate some fields on different tables and leave the objects separate . Is there a problem created by having clients and Sales Representatives in separate tables with the same columns? Certainly the Data Model has more tables but it many ways it is way easier to understand and query. One of the main reasons I would do this is how generic entities can complicate querying and reporting. Although it can provide a more aesthetically pleasing data model, the opposite could be said of the queries that are required to pull information out of the database. And if the queries and reports are more complicated, the same could be said of the Extract, Transform, and Load processes to load the data.
Going forward, I think I will resist the urge to combine similar entities when there isn’t a business reason to do so.
How about you?