We have a SQL Server development time script. We must store data about different organizations in our database (for example, Customer, Vendor, Distributor, ...). All diff organizations use the same type of information (almost) .. for example, address data, etc. And they will be transferred to other tables (i.e., connected via OrgId, and we need to look for OrgName in many places of faults)
I see two options:
We create a table for each organization, such as OrgCustomer, OrgDistributor, OrgVendor, etc. .... all tables will have a similar structure, and some tables will have additional special fields, for example, the client has a HomeAddress field (other Org tables do not have ).. and vice versa.
We create a common OrgMaster table and save ALL DIFFERENT ORGES in one place. There will be an OrgType field in the table to distinguish diff types from Orgs. And special fields will be added to the OrgMaster table (only the corresponding Org records will have values in such fields, in other cases it will be NULL)
Some pros and cons # 1:
PROS:
- It helps to distribute the load when accessing the data type of type Org, so I believe that it improves performance.
- Provides full flexibility to adapt any particular Org table without using other existing Org types.
- Not sure if diff indices on distributed / distributed tables work better than one large table.
MINUSES:
- Replication design. If I need to increase the size of the ZipCode field - I have to do this in ALL tables.
- (.. CRUD, n-fold.. 3-4 Inert SP, 2-3 SELECT SP ..)
- n- DB\ SP Business .
- () .
# 2:
:
- N-fold 1 : -)
- , (.. SP CRUD ..).
- . .
:
№ 3 - Org, OrgAddress . №1 №2, !
, , , , , , , .
. .
Hemant