March 10, 2008 at 10:11 am
Hello!
I´m having some trouble choosing my primary key for a database I´m currently developing. I´m not that experienced with developing databases, although this is not my first.
I have an OrderLookup table, where the PK is the PurchaseOrderID.
I also have an OrderInput table, where the user input will be stored.
The PurchaseOrderID will exist in this table also, and all records
must have a corresponding PurchaseOrderID in the OrderLookup table.
My issue here is that in the OrderInput table, one PurchaseOrderID
can exist many times, so I´m not really sure how to set up my PK for this table.
I´m thinking maybe I should just create an identity column and use as PK,
and have PurchaseOrderID be a FK referencing OrderLookup.
Would that be a good idea? Since like 98% of all queries will have a "where PurchaseOrderID = xxxx", would it be a good idea to move the clustered index from the PK to the FK?
Other thoughts?
Thanks in advance!
March 10, 2008 at 11:28 am
Dli,
Data models for Purchase Order are well known and you don't need to reinvent it from scratch. I suggest that you start Google and type "Purchase Order Data Model" (and press Enter :)).
Alternatively, you may check one of sample applications provided by Microsoft. The data model you need is already there.
It seems to me that in your case the OrderLookup table may be named just PurchaseOrder and the OrderInput table may be called PurchaseOrderDetails (to have more meaningful names).
For each order in PurchaseOrder table you may have one or more records in PurchaseOrderDetails table. They just correspond to items of a given order. So if you add a column like ItemNo or LineNo and set the primary key to a composite index consisting of PurchaseOrderID and ItemNo, it may be exactly what you need.
But I strongly suggest analysis of existing data models - especially if you say that you are not experienced in data modelling. Check whether they meet your requirements and modify them only if you need more functionality then it's already included.
Marek
March 10, 2008 at 12:27 pm
Hello and thanks for your input!
The Order/OrderDetails model doesn´t really apply in my case.
This database will be used to keep record of problems regarding purchase order intakes.
Since one purchase order might be partially delivered at different dates, I need to be able to keep records of one purchase order occuring one or many times (in OrderInput).
The issue is that there´s not really any other business key to use besides the purchase order id, so a composite key would need to consist of the purchase order id and a datetime value or such, and I have a feeling that´s a bad idea?
March 10, 2008 at 1:05 pm
One of the primary attributes of a Primary Key is that is must be unique - so your purchase order ID cannot be the PK of this particular table (since it will appear in multiple records). At best it can (and should) be a foreign key to the PurchaseOrder table.
If you don't have another set of fields that naturally make up a unique and non-nullable identifier by row, then perhaps go with the IDENTITY field like you mentioned (or if the values in the PK are likely to change a fair amount as well).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 10, 2008 at 1:49 pm
I have an OrderLookup table, where the PK is the PurchaseOrderID.
You actually named a table "OrderLookup" to hold Purchase Orders and gave it a Primary Key on a column called "PurchaseOrderID"???? :blink:
--Jeff Moden
Change is inevitable... Change for the better is not.
March 10, 2008 at 2:31 pm
If this is a bad idea, please tell me why! I said I´m pretty much a beginner!
March 10, 2008 at 2:51 pm
There are, of course, exceptions to every rule... but most people name a table after what a single row in the table holds. For example, the Customer table would hold the necessary information for a single customer and, if you're using the "ID" paradigm (not going to argue if it's right or wrong here 😉 ), the Primary Key would be on the CustomerID.
If a table holds the header information for a Purchase Order on each row, the table would be named PurchaseOrderHeader and it would have a Primary Key on the PurchaseOrderHeaderID column...
In other words, lots of folks agree it's a pretty good idea to have the Primary Key column of the table be named the same as the table followed by "ID".
Like I said, there will be exceptions and there will be those that do it another way. I've found the "ID" paradigm I mentioned to be one of the easiest for Developers to remember and use.
Since you said you're new to the design process, you might want to get a good book on it or maybe do a Google search. There's some really good ideas and methods on designing and naming objects in databases.
I do know that the way you did it would probably not fit any of the recommended methods. Not trying to be a smart guy here... just friendly advise.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 10, 2008 at 2:56 pm
Thanks for you advice, really appreciate it!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply