Considering a procurement system as an example. Would it make sense to have one single table for the following: Quote, Order, Invoice and Credit Memo?
There will be some details that are different. For example each list will have its 5 special fields but the other say 50 fields would be exactly the same. Would it make sense in this case to join all tables in a single one. And some fields are unused for some types.
I can see that Navision system does something like that in its database. It uses one table, but then splits into separate tables once the items are posted. I think this may be for performance reasons because the posted items can tend to become very large. & splitting into separate tables would speed up queries. But this is just my thinking.
So my main dilemma is … what is the recommended approach in such case: 1 table or separate tables? what are the pros and cons of each? What are the business considerations to take into account?
Thanks for your help!
Best Answer
There are essentially three options (with the possibility of complexity via combination of the three options):
Here are some of the factors to consider:
Based on these considerations, I would personally keep the tables separate. Even though they may have similar attributes, they are fundamentally different entities.
What I would try to do, however, is look at those sets of common attributes and see if they really belong on the Quote/Order/Invoice/Credit Memo, or if normalizing those out into their own tables would be a better idea. For example, if you have customer first name, customer last name, customer street 1, etc., splitting that out into a customer key (or customer and address keys, depending upon circumstances) could cut down on the total number of columns in each of the four major entities, reducing repetition and gaining the other benefits of normalization.
It's possible that you've already normalized everything out to the fullest extent and that each of those entities does, in fact, legitimately have 50 attributes which are in common. In that case, I'd still keep separate tables because you're talking about fundamentally different things which happen to relate to one another.