Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jun 2009
    Posts
    6

    Common problem in my DataBase design !

    Hello all,

    this is a common problem in all my database design ,

    let's say I have a three types of Clients... ClientA , ClientB and ClientC ... Completely different in their properties ! .. so I stored them in three different tables !!

    So , I have a Safe and SafeTransactions ... in SafeTransaction I put fields like {trnID , trnClientID, trnValue, trnIn, trnDate ..... } , and stopped here !!!

    How can I retrive the client from the transaction !! what's the modification I need ?!

    thanks in advance

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you can have separate "subtype" tables for different types of client, provided that you have a "supertype" table which holds the common columns that all clients have

    any tables that have a relationship with clients will have the supertype's PK as their FKs

    do some research on "supertype/subtype"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2009
    Posts
    66
    Quote Originally Posted by the_knight5000
    Hello all,

    this is a common problem in all my database design ,

    let's say I have a three types of Clients... ClientA , ClientB and ClientC ... Completely different in their properties ! .. so I stored them in three different tables !!

    So , I have a Safe and SafeTransactions ... in SafeTransaction I put fields like {trnID , trnClientID, trnValue, trnIn, trnDate ..... } , and stopped here !!!

    How can I retrive the client from the transaction !! what's the modification I need ?!

    thanks in advance

    That is called table splitting based on the type of client. Do you split your Personnel table into MalePersonnel and FemalePersonnel?

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by the_knight5000
    let's say I have a three types of Clients... ClientA , ClientB and ClientC ... Completely different in their properties ! .. so I stored them in three different tables !!
    You are right so far but there are probably also some columns they have in common. Assuming a client is identified by the same key attributes in each case then create a super-type client table consisting of the key and any other common attributes. Other tables reference the super-type table.

  5. #5
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by sqlguru
    Do you split your Personnel table into MalePersonnel and FemalePersonnel?
    If they have different attributes that don't apply to both Male and Female then yes I would split them between two tables.

  6. #6
    Join Date
    Jun 2009
    Posts
    6
    ok I totally agree with all of you on this design, but there some problem :

    having several tables for details "SubTables" based on the type of the Client , that means using -Cases - "If" or "Switch " to determine the type so get the proper subtable ,Isn't it ?

    I'm trying to put a very abstract & Scalable Design and this idea of "Cases" doesn't go with Scalability !

    any suggestion for better ideas ....

    thanks in advance

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by the_knight5000
    having several tables for details "SubTables" based on the type of the Client , that means using -Cases - "If" or "Switch " to determine the type so get the proper subtable ,Isn't it ?
    no it isn't

    let's get specific, shall we?

    could you give us examples of the columns that are different between the different types of clients? how many common columns do the different types of clients have, as compared to unique columns?

    maybe you should just use a single table which contains all columns for all types

    it's hard to make this decision without knowing more about your data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2009
    Posts
    6
    ok , thanks very much for the VERY fast reply .... preparing a good example with explanation ....

  9. #9
    Join Date
    Jun 2009
    Posts
    66
    There is no "if else" in the relational model, it's set based.

    Post your DDL so we can see if the clients really have different properties or if it's a design problem.

  10. #10
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by the_knight5000
    having several tables for details "SubTables" based on the type of the Client , that means using -Cases - "If" or "Switch " to determine the type so get the proper subtable ,Isn't it ?
    You just need an outer join. If you make sure that the same attribute doesn't appear in more than one place then you should never have to reference more than one table with the same piece of logic.

  11. #11
    Join Date
    Jun 2009
    Posts
    6
    http://img132.imageshack.us/img132/7165/dbproblem.png

    that's what I'm talking about ... I found giving an example on documents type will finish the clients Similarity point !!


    the point is in Safetransaction Table , I just want to put a better design that enables me to get the Document Information (Header + Details) back without checking the type of Document " Cases "

    please tell me if the example was not clear enough....

  12. #12
    Join Date
    Jun 2009
    Posts
    66
    Is poClient and invClient foreign keys to a client table?
    Is trnDocumentId the actual poId OR invId DEPENDING on trnDocumentType?

    Your design is all wrong. There should be "safetransaction" tables for purchase orders and invoices (also, shouldn't invoices belong to purchase orders with a received date??) You have two tables as one table that depends on a flag!

  13. #13
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Try this:

    SELECT t.trnID, t.DocID, ....
    FROM SafeTransaction t
    LEFT OUTER JOIN PurchaseOrder_Details p
    ON t.DocID = poID
    LEFT OUTER JOIN ReceiveInvoice_Details i
    ON t.DocID = i.invID

  14. #14
    Join Date
    Jun 2009
    Posts
    6
    @sqlguru :
    1-yes, poClient and invClient foreign keys to a client table
    2-yes, and there are many types of documents not such these two !

    I know this is a bad design and that what I'm asking for , how to pput a better one ?
    Could u please explain ur point in putting a better a Design ! ... with taking care about performance issue !


    @dportas:
    I have a several Types of Documents not only two !
    I think this solution will not be practical as I need the data as Union form ! not in separated fields !


    any suggestions !

  15. #15
    Join Date
    Jun 2009
    Posts
    6
    @sqlguru :
    1-yes, poClient and invClient foreign keys to a client table
    2-yes, and there are many types of documents not such these two !

    I know this is a bad design and that what I'm asking for , how to pput a better one ?
    Could u please explain ur point in putting a better a Design ! ... with taking care about performance issue !


    @dportas:
    I have a several Types of Documents not only two !
    I think this solution will not be practical as I need the data as Union form ! not in separated fields !


    any suggestions !

Posting Permissions

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