There are some things that I am in a dilemna with about general database/table design issues. 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 common 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)
b. performance
c. flexibility (can it easily adapt/grow to future changes/enhancement)
d. others

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.

1. it depends

the example of inventory transactions would be a great candidate for a single table

the example of PO number and Job Order number being different numbers (i.e. key values) argues strongly for different tables

2. it depends

i allow nulls, some people don't

my "good number" is no more than two thirds of the columns null in any one row

In an inventory system, all types of transactions will end up to an IN and an OUT to your inventory. So, all sales, purchases, adjustment, issuance--although they may have slightly different fields in the header (or in the detail) they have a lot in common in the detail, like:
- the item that was involved
- the qty
- location/warehouse of item
- cost/value of the inventory

Do you create an ancestor table for all the detail tables? What about the header tables?
And do we create separate tables for descendants, even if only 1 minor field is differentiating it from other types?

What I do know is that if you don't create an ancestor table, reporting will be more difficult. It will involve a lot of UNIONs. Like, i want to get the total qty on hand, I would have to get the sum from different tables using UNION. With an ancestor table, we could just get the sum from 1 table. Or are unions a better idea to you?

There are cases when there are different ways to normalize. This is related to my question 1 in the first message.

For example, we have a simple inventory database tracking the quantities that go in and go out of our stock. (Let's make this a simple example. Assume the following basic fields only. And let's assume there is only 1 item). There will be only 3 types of transactions: Purchase, Sale, Adjustment. For purchases, we want to know who the supplier is. For sales, we want to know who the customer is. That's it. So, w/c of the following is the best approach:

(Let's leave out the other master data like Supplier/Customer. I think u can get the point)

SOLUTION 1: (each transaction in their own table)

Purchase (table)
PurchaseID,Date,Quantity,SupplierID
1,6/18/2007,10,A
2,6/19/2007,5,B
3,6/20/2007,1,C

Sale (table)
SaleID,Date,Quantity,CustomerID
1,6/18/2007,2,D
2,6/19/2007,1,E
3,6/20/2007,3,F

Adjustment (table)
AdjustmentID,Date,Quantity
1,6/20/2007,-1 (1 unit was lost)


SOLUTION 2: (common fields in 1 table. OO approach)

StockTransaction (table)
StockTransactionID,Type,Date,Quantity
1,Purchase,6/18/2007,10
2,Purchase,6/19/2007,5
3,Purchase,6/20/2007,1
5,Sale,6/18/2007,-2
6,Sale,6/19/2007,-1
7,Sale,6/20/2007,-3
8,Adjustment,6/20/2007,-1

Purchase (table)
PurchaseID,StockTransactionID,SupplierID
1,1,A
2,2,B
3,3,C

Sale (table)
SaleID,StockTransactionID,CustomerID
1,5,D
2,6,E
3,7,F

Adjustment (table)
AdjustmentID,StockTransactionID
1,8
(NOTE: This table could be eliminated)

END

So, Solution 1 or 2? My thoughts on both solutions:

1. Both are normalized. It's just a matter of different object orientated hierarchy design.
2. Solution 2 makes it easier to get the total quantity on hand. Solution 1 would require unions to get the total quantity on hand or other reports involving quantities. Solution 2 also makes it a lot easier to combine a summary of all transactions.
3. Solution 2 can still be denormalized if necessary for performance reasons.
4. Solution 2 can have a problem where later, business rules may change and what you thought was a common field is no longer a common field. And you might have to resort to Nullable fields (w/c may cause denormalization because they are not dependent on the primary key--they are dependent on the Type). And it might not be easy to transfer the common field to it's descendants to make it uncommon. Solution 1 suffers less from this.