DB design for master file in enterprise software

Question

Dear all.

I want to write an enterprise software and now I'm in the DB design phase. The software will have some master data such as Suppliers, Customers, Inventories, Bankers...

I considering 2 options:

• Put each of these on one separate table. The advantage: the table will have all necessary information for that kind of master file (Customer: name, address,.../Inventory: Type, Manufacturer, Condition...). Disadvantage: Not flexible. When I want to have a new type of master data, such as Insurer, I have to design another table.

• Put all in one table and this table have foreign key to another table which have type of each kind of master data (table 1: id, data_type, code, name, address....; table 2: data_type, data_type_name). Advantage: flexible - if I want more master data such as Insurer, I just put in table 2: code: 002, name: Insurer, and then put detail each insurer into table 1). Disadvantage: table 1 must have sufficient field to store all kind of information including: customer name, address, account, inventory's manufacturer, inventory's quality...).

So which method do you usually do (or you think work better). Thank you very much

Answer 1

logically, each "master" entity should be in its own table

if you don't, you'll find joins will become very painful, and your generic lookup table will accumulate all kinds of useless fields

Answer 2

I would advise creating separate tables for each entity type - it will be a lot easier to maintain in the future when you discover things you want to add for one entity type that don't apply to the others. If all of the entities (Suppliers, Customers, etc) are going to have the same fields and the only difference is their type then you could theoretically use one table. However, I would expect that there would be enough differences between the entity types that it would be worth creating separate tables for each. If there are several fields in common (e.g. address information) you could create a table for the common elements and have a foreign key in the individual tables to the table with the common data (e.g. AddressID).