Ordering System Datamodel !
I am designing an ordering system.
I need to be able to accept orders from different objects:
In other words I can charge a purchase to a devision, person or a job.
My problem is that I can't decide which design is better:
1.Having a SuperType entity for orders and then one subtype for each obejct (OrderSubPerson,OrderSubDivision ...). This design will enable me to better manage data integrity, however the application developemnt will be harder.
2.Have one table for orders and in that table have a field for ObjectType and another field for the primary key of a specific record in that object. The problem then is that I can't create refrential integrity cause I have different data types in that field.
3.Create a new table called BillingToAccount (BillAcctID,BillAcctName,BillAcctTypeID) and subtype tables for each object:
tblBillAcctSubDivision(BillAcctID,DivisionID),tblb illAcctSubJob(BillAcctID,JobID)... .On my Orders table I will have a field for BillingAcctID that will be linked to the supertype billing account. This way I can manage the data integrity better but its more work for the application developers and also more load on the server since I have to create a billing account for every new entry in the Person,Job or Division table.
In addition to that I don't really have additional fields if an order is for a specific object so the subtype table will only hold the ID of that object.
Which design should I go for?
The intellect is better than desire, for the intellect makes you a king over your destiny, and desire makes you a slave of your destiny.