Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Storing orders using product id fields

 
Dave Bosky
Ranch Hand
Posts: 72
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 72
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 399
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 7023
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Moving this to the JDBC forum...
[ December 16, 2003: Message edited by: Dirk Schreckmann ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic