File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Storing orders using product id fields Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Storing orders using product id fields" Watch "Storing orders using product id fields" New topic

Storing orders using product id fields

Dave Bosky
Ranch Hand

Joined: Dec 16, 2003
Posts: 72
I have an order form that allows a user to select a product and customize the product by choosing from a list of available features.
When I save the order should I store the primary key(pk) of the product or the product name in my order table? It seems like saving the product name would be better. If the product is deleted from the catalog I won't be able to lookup the product name using the primary key but if I save the product name it won't matter.
Thoughts? How is everyone else doing things like this?
Thomas Rochon
Ranch Hand

Joined: Jul 11, 2002
Posts: 72
Hi Dave !
I guess commonly it's better to store the primary key of
the given product into the table. Well - I think that's
the common way ...
Maybe one day you'll have to handle two different products
with the same name (you never know). Or maybe the name of
the product will change. In this case you won't have to
change thousands of table entries.
But I'm a bloody newb with databases.
Best regards ! - Thomas -
[ December 16, 2003: Message edited by: Thomas Rochon ]
chris czinder

Joined: Oct 02, 2002
Posts: 28
Personally I would recommend assigning a Primary Key, and then checking to make sure that the product doesn't exist by checking any new names vs the ones you already have.
Wayne L Johnson
Ranch Hand

Joined: Sep 03, 2003
Posts: 399
I'm with Thomas ... as a general rule you should always store the primary key, not the name. You can view it sort of like OO "encapsulation" where the ORDER simply has a FK (reference) to the PRODUCT, w/out needing to know anything about how the PRODUCT is defined.
In addition to the advantages Thomas pointed out (ease with which product name is updated, ability to have multiple products with the same name), you have a smaller data set. If you later decide that the product name column needs to be lengthened, you affect only the PRODUCT table, and not others.
As for the deletion problem, you probably don't want to ever delete a product. It's much better practice to have a 'status' field in the PRODUCT table; when a product is no longer supported, you can set the 'status' to 'inactive' (or whatever you want to use) so that it no longer appears as an option, but all of the old ORDER records still have a valid reference.
Dirk Schreckmann

Joined: Dec 10, 2001
Posts: 7023
Moving this to the JDBC forum...

[How To Ask Good Questions] [JavaRanch FAQ Wiki] [JavaRanch Radio]
I agree. Here's the link:
subject: Storing orders using product id fields
It's not a secret anymore!