We're revamping our merchandise that will get highly personalized for every client. Presently we must maintain separate database for every client which heavily affects persistence layer.

Our major goal for brand new database schema is extensibility. I'm not a specialist database designer thus require input from experts :).

I'm considering extension (sister) table approach. For each core table there might be a sister table where extended fields would reside. By doing this, we are able to develop separate persistent layer for core and extension tables.

Let's say i divide the it into two databases. Core and Extension databases. This can ease the constant maintenance.

Making separate databases won't permit you the native Referential Integrity using PK and FK. Additionally, it adds compexity to maintainance.

Should you RDBMS supports schemas, you need to make the most of multiple schemas in one database.

Also, if you're a new comer to databases, you need to find out about normalization and database design (you will find good quality threads on this website).

In PostgreSQL, you could have inherited tables. For instance, for those who have table A that consists of posts (x,y,z), you could have Table A-sub-1 that gets from the but additionally adds posts (i,j,k) and Table A-sub-2 that contributes posts (d,e,f) rather.

Just one benefit for this is the fact that a number of your Choose claims can stay: Choosing in the parent also chooses all child tables automatically. Individuals statments that onlyuse the most popular elements wouldn't have to change. Place/UPDATE/Remove claims will have to be personalized to visit from the specific derived table.

Be cautioned, there's an expense to become compensated here for the reason that PKs and FKs cannot be relevant to the general structure. Each inherited table continues to be a completely independent table on its own and handles its very own constraints. That's, you cannot possess a PK or FK around the parent table and also have it instantly affect all of the children, too.