DB Design issue - Foreign Key
-
Hi all,
What is the best way to design the following: There are 3 tables, Documents, Clients, and Projects, with the following
columns in each table:
Table: Documents
DocumentID
ClientID
ProjectID
Table: Clients
ClientID
Table: Project
ProjectID
ClientID
So I can be working on documents that are related to a specific project, which themselves are related to a client .
My question here is: Is it good practive to store the ClientID in the Documents table or not, as I can get to the Client
through the Project table, so is it necessary to store the ClientID in the Documents table, or can it lead to inconsitences?
Thanks
Tryst
-
If you see that you will often join the document table to the client table to look up client information using a DocumentID, when you have no need to also select from the project table, then including the ClientID in the document table makes a lot of sense in that it saves the extra join. (A little mild denormalization to speed queries never hurt anybody)
If, however, if most or all of your queries to the document table that include a join to the client table will also join to the project table, then you get less bang for the buck.
Regardless, if you find you often issue range queries such as 'select all documents for client X where document.property = value', then the inclusion of the ClientID, along with an appropriate index, can slash query times.
-Eddie
Eddie Wuerch
MCM: SQL -
Call me crazy
, but I'd do this the normalised way (i.e. with no clientId in the documents table) until I found a good reason not to... 
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
If there are no Projects involving multiple Clients for whom you wish to keep separate Documents (e.g. ProjectA, Client1 has Doc1, Client2 has Doc2 and both have Doc3), then there is no requirement for the ClientID in the Documents table. If you have a large number of clients and orders of magnitude more documents you will probably see improvement in queries as described by Eddie.
HTH,
Art
-
Thanks for the reply guys!
I will look into the best approach when I get into work on Monday.
Tryst
-
First I have to really understand what has Document have to do with Client and to Porject, and what does Client have to do with Project and Document, and what does Project have to do with Document and Client?
At first glance looks like a circular reference whihc is frowned upon.
But, does Client_ID in project mean the same as the Client_ID in Document?
What if the client_id project means the clients involved in the project and the client_id means the person who created the document. Then you need it in both tables because they do not mean the same thing. In addition it may be that a document can only be created by a client on the project do therefor you need to have a foreign key against projects project_id and client_id from those in documents unless you can have clients create documents for projects they are not on or you may have clients come and go but need to retain their documents on the project (you might consider dealing with in Projects as well to keep the historical facts).
However, if you can only have one client per project and documents can only be created by said client then you document need the client_id in documents because only the fact a document exists is of concern.
Maybe even all that matter is if there is a document for a project and not who created it, then no client_id is needed in documents.
You should sit down and write out what each table and the columsn actually represent then consider the model to see if you need to change anything.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply