Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Rob Spoor
  • Bear Bibeault
Saloon Keepers:
  • Jesse Silverman
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • Al Hobbs
  • salvin francis

Storing orders using product id fields

 
Ranch Hand
Posts: 72
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Ranch Hand
Posts: 72
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 399
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Sheriff
Posts: 7023
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Moving this to the JDBC forum...
 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic