I am working on creating an application with multiple parent accounts each of which has different multiple users. Each account consists of a set of data of similar type but needs t be maintained separately. eg. inventory of each organization which their respective users can view.

What is the best practice:

1: Create different database tables for each organization

2: Create a common table and have an extra column for the organization it belongs to.

2 Answers

Community On

As mentioned, do a one table for organization, one for equipment, one for persons and so on. It is step 1 - separate table for separate entity.

After that connect them with relationships: primary key in main entity to foreign key in sub entity. Other words every row in equipment table would have column with id of organization it belongs to. And so on.

There are many other circumstances, including subdividing entities to such called normal forms, you can study it if it needed, to reduce data consistency supply costs. But it could also negatively affect performance.

Anyway: same class entities commonly should be stored in one table.

serge On

The best practice in OLTP (transaction processing) is to create a common table and to implement a subtyping in some way, for example "have extra tables with columns for the organization subtype". In OLAP (analytical processing) warehousing it is still a good practice but the mapping of subtypes can be implemented differently. In OLAP datamarts the solution "one table per organization" can be a good practice.

You may have a look on the book "Programming with databases" which covers these topics: subtype/subclass mapping, OLTP vs OLAP, denormalization etc.