Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164

    Ordering System Datamodel !

    Hi,
    I am designing an ordering system.
    I need to be able to accept orders from different objects:
    1.Person
    2.Division (departments)
    3.Job Accounts

    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.

  2. #2
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    I choose the first. Let application development be a bit harder to reduce the total work of the user.

  3. #3
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    Thanks, finally a reply!

    I actully choose the first option too.
    The application development will be an one time effort, but the data integrity will benefit for a long time.

    The other advantage of this appraoch is the flexibilty of the system. If in the future a need rises to record specific information for a subtype not requried for others then the system could easily handle that, however if we take other approaches then we will end up with lots of NULL fields.

    Thanks for your reply.
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •