I wish to write a company software and today I am within the DB design phase. The program may have some master data for example Providers, Clients, Inventories, Bankers...
I thinking about 2 options:
Put all these on a single separate table. The benefit: the table may have all information you need for your type of master file (Customer: title, address,.../Inventory: Type, Manufacturer, Condition...). Disadvantage: Not flexible. When I wish to possess a new kind of master data, for example Insurance provider, I must design another table.
Put all-in-one table which table have foreign answer to another table that have kind of each type of master data (table 1: id, data_type, code, title, address.... table 2: data_type, data_type_title). Advantage: flexible - basically want more master data for example Insurance provider, I simply place in table 2: code: 002, title: Insurance provider, after which put detail each insurance provider into table 1). Disadvantage: table 1 should have sufficient area to keep all type of information including: customer title, address, account, inventory's manufacturer, inventory's quality...).
So which method would you usually do (or else you think are more effective). Thanks greatly
realistically, each "master" entity ought to be in the own table
if you do not, you will find joins will end up very painful, as well as your generic research table will accumulate a myriad of useless fields
I'd advise creating separate tables for every entity type - it will likely be a great deal simpler to keep later on whenever you uncover things you need to add for just one entity type that do not affect others. If all the organizations (Providers, Clients, etc) will have exactly the same fields and also the only difference is the type then you may theoretically play one table. However, I'd expect there could be enough variations between your entity types that it might be worth creating separate tables for every. If you will find several fields in keeping (e.g. address information) you can produce a table for that common elements and also have a foreign type in the person tables towards the table using the common data (e.g. AddressID).