Extensible Database Schema

Question

We are revamping our product which gets highly customized for each client. Currently we have to maintain separate database for each client which heavily affects persistence layer.

Our major goal for new database schema is extensibility. I am not an expert database designer thus require input from experts :).

I am thinking about extension (sister) table approach. For every core table there could be a sister table where extended fields would reside. This way, we can develop separate persistent layer for core and extension tables.

What if i divide the it into two databases. Core and Extension databases. This will ease the maintenance.

Answer 1

Making separate databases will not allow you the native Referential Integrity using PK and FK. It also adds compexity to maintainance.

If you RDBMS supports schemas, you should take advantage of multiple schemas in a single database.

Also, if you are new to databases, you should learn about normalization and database design (there are some good threads on this site).

Answer 2

In PostgreSQL, you can have inherited tables. For example, if you have table A that contains columns (x,y,z), you can have Table A-sub-1 that inherits from A but also adds columns (i,j,k) and Table A-sub-2 that adds columns (d,e,f) instead.

One benefit to this is that some of your SELECT statements can stay the same: SELECTing from the parent also selects from all child tables by default. Those statments that onlyuse the common elements wouldn't need to change. INSERT/UPDATE/DELETE statements would need to be customized to go against the specific derived table.

Be warned, there is a cost to be paid here in that PKs and FKs cannot be applied to the overall structure. Each inherited table is still an independent table in its own right and handles its own constraints. That is, you can't have a PK or FK on the parent table and have it automatically apply to all the children, as well.