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