February 19, 2009 at 9:32 am
Here is my database design:
tblDocument
PK DocID int, not null
FK DocTypeID smallint, not null
FK DivisionID smallint, not null
DocDesc nvarchar(50), null
Filename nvarchar(25), null
Filepath nvarchar(100), null
InsertDate datetime, null
UpdateDate datetime, null
FK LangID smallint, null
tblPDF
PK PDFID int, not null, related to DocID 1:1
MNumber nvarchar(15), null
ModelID smallint, null
PDFTypeID smallint, not null
tblModel
PK ModelID smallint, not null
ModelDescription nvarchar(100), not null
FK BrandID smallint, null
FK ProductTypeID smallint, not null
FK SizeID smallint, null
These are my three main tables I deal with. There are six other "lookup tables". tblDocument is my main table. I need to create numerous queries based on what information someone wants about a document. For example, I may want the Division, DocDesc, Filename, Filepath, BrandName, PDFType and TypeName of a document. This involves pulling data from the tblDocument but also using the tblPDF, tblPDFType, tblModel, tblBrand and tblDivision.
The relationships are a little complicated between the tables. tblDocument and tblPDF are related on a 1:1 basis on id number. tblPDFType and tblModel are directly related to tblPDF. tblBrand, tblProductType and tblSize are related directly to tblModel. So to get to Brand I have to go from tblDocument -> tblPDF -> tblModel -> tblBrand, and I will probably want to display the BrandName rather than the id number.
What I am looking for is a rule-of-thumb for constructing queries to access Document information from tblDocument and other related tables that are nested in deeper layers. I will almost always want to display information from the lowest level along with columns in the tbldocument. I'm new at this. This is really my first database project. I feel like I'm putting together a jigsaw puzzle. 😀
February 19, 2009 at 12:14 pm
I do not know if everyone here is going to agree with me but I got two comments
1. Try to avoid 1:1 relationships - make it into one table = less joins in the query.
2. Whenever designing the database keep in mind that the database has two basic functions - to store the data and to retrieve the data. So you do not want to design your database in a totally abstract manner. You have to know how you going to store the data and how are you going to retrieve it before you commit to your database schema. In some cases you may want to de-normalize your tables so the retrieval of the data is easier and faster. If you have multi layer relationships you may want to carry over the top layer IDs to the lower layers.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
February 20, 2009 at 5:45 am
Thank you, I changed the design to make it simpler.
When I started this was supposed to be a PDF database, but then my requirements changed to make it a documents database. I eliminated the PDFTable and the ModelTable and moved their foreign keys into the Documents table. This is going to be much easier to query.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply