There are some things that I am in a dilemna with about general database/table design issues. Each has its pros and cons. What do you think are the best practices? Here are some problems.
1. You have 2 tables (or sets of tables) that are exactly/almost exactly the same (in fields and conceptually they are also similar). Do you combine them into 1 table (or 1 set of tables) with an additional field to differentiate/classify them as to w/c type they are (more like the Object-Oriented approach)? Or keep them separate?
To what degree to do you have common tables? Because you can do it completely like OO and have a hierarchy that starts from Object and have all tables descendants from Object and so on.
some general examples:
* Inventory Receipts/Inventory Issuances/Inventory
Purchases/Inventory Adjustments/Sales/anything related to the ins and outs of inventory items.
- Do you have common table(s) for all or do you completely separate each transaction into its own set of tables?
* Job Order/Purchase Order
- Some fields may be different like the PO number and Job Order Number w/c might have different sequences. And the detail may contain different contents where one is about labor/work and the other is about inventory.
What do you think are the best practices? And the pros and cons of each in terms of:
a. ease of usage (coding/reporting)
c. flexibility (can it easily adapt/grow to future changes/enhancement)
2. When combining similar tables, do you completely separate non-common fields into their own descendant tables? or just put the fields in and allow NULL values? If you decide to put nullable fields, how many fields do you think is a good number to decide when separation into different descendant tables is appropriate?
These are common problems most database designers encounter. I hope some of you can shed light into these.