All geek questions in one place

Extensible Database Schema

We update our product, which is customized for each client. Currently, we must maintain a separate database for each client, which greatly affects the retention rate.

Our main goal for the new database schema is extensibility. I am not an expert on creating a database, so I need an expert :).

I am thinking of an extension approach (sister). For each main table, there may be a sisters table in which extended fields will be located. Thus, we can develop a separate constant level for the main and additional tables.

What if I divide it into two databases. Base and Extension. This will facilitate maintenance.

+3
source share
2 answers

Creating separate databases will not allow you to use referential integrity using PK and FK. It also adds complexity to maintain.

If RDBMS supports schemas, you must use multiple schemas in the same database.

In addition, if you are new to databases, you should learn about database normalization and development (there are good threads on this site).

+1
source

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

, SELECT : SELECT . , , . INSERT/UPDATE/DELETE , .

, , FK . - . PK FK .

+1

Source: https://habr.com/ru/post/1751001/

More articles:


All Articles