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

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"

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

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.

If they have different attributes that don't apply to both Male and Female then yes I would split them between two tables.

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

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

ok , thanks very much for the VERY fast reply .... preparing a good example with explanation ....

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.

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.

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....

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!

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

@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 !

@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 !