Reusability In Databases


What We See Commonly:

Have you ever thought of removing redundancy from your databases? This is a common practice that every entity is defined separately and if any entity member is common among many entities we don’t bother it as long as it defines a good Entity Relationship Design. For example two tables containing same fields.

How To Remove It:

There could be many ways. The first thing a DB Designer would come up with is defining Many-To-Many relations but I would say if you want to kill performance then there could be other better ways (Sarcasm? yes it is!).
How about combining the entities? Yes! If two entities have more than 50% commons fields then why not combine them?

Example 1:

Recently I did an analysis on a services seller solution. There were two types of services both entirely different and both required different front-end interfaces (and controllers of course). But both had a price and delivery time as common. As solution, tables were combined to facilitate two different entities using one extra column differentiating entity in table. Simple isn’t it?

Example 2:

The same system had another interesting functionality required which was leading to “increasing tables in DB”, it was defining a different price for a certain User Group or for a certain User. The first solution that would come in any developer’s mind would be having two new tables, one with fields custom_price, services_id and user_group_id and other table with custom_price, service_id and user_id.
The solution I later came up with was making single table with fields user_id, user_group_id, custom_price, and service_id. For both purposes same table could be used and of course one of the fields among user_id and user_group_id will be kept null in any particular entry.

Does It Effect Entity Relationship Design?

Designing any system’s Entity Relationship Diagram let developers know about entities in the system so system can be designed accordingly. Re usability in DB does not affect ERD in any way because entities should be created separately in Model (at programming level) it is not bound to DB. But one may consider redundancy while creating different Model classes possessing queries on same table with different fields.

Removing Redundancy in Model:

You might think that it’s not much gained while applying reusability in database and still have to make independent model classes with more complex code. Let me tell you about the best friend of an Object Oriented Programmer i.e. Polymorphism. Make a parent class have it generic to the related table and inherit child Model classes to make custom use with respect to fields. Two birds is one stone.