MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Bruce Feist Date:June 19 2003 3:38pm
Subject:Re: selecting PRIMARY KEY when there is no unique value
View as plain text  
Mojtaba Faridzad wrote:

>In a docuement such as Invoice Form, we have a header and a couple of
>records for the detail. In header table, Invoice# can be the PRIMARY KEY but
>in detail table, Invoice# is not unique. I think there are two solutions to
>choose a Primary Key (in MyISAM type) :
>
>1) Adding an id field ( auto_increment ) and choose it as PRIMARY KEY
>in this case we have to add another index on Invoice# for making relation
>with the header table
>
>2) There is another field in detail table with "timestamp" type for keeping
>the last change on the record. I want to select ( Invoice# + myTimestamp )
>for PRIMARY KEY. in this case I don't need to add a new fields ( id ) and
>another index ( on Invoice# ) to the table.
>
>which one do you prefer and usually use?
>  
>

My rule of thumb is to use a composite key such as you suggest in (2) in
cases where the row doesn't really represent a distinct business entity,
but is only used as part of a more complex one.  Your example of
multiple details within an invoice form falls into this category.  If
the row is likely to be used on its own, I'm more likely to introduce a
new field as a key, in order to make it more convenient to access it
directly.

In other words, the trade-off is in simplicity of database design (use
the existing fields) versus simplicity and efficiency in doing
single-row look-ups.  The latter is only significant if you expect to be
retrieving the row on its own.

Bruce Feist



Thread
Odd thingDave Christensen19 Jun
  • Re: Odd thingDon Read19 Jun
RE: Odd thingJay Blanchard19 Jun
RE: Odd thingDave Christensen19 Jun
  • RE: Odd thingMatthew Smith19 Jun
RE: Odd thingJay Blanchard19 Jun
RE: Odd thingDallas Dickey19 Jun
  • selecting PRIMARY KEY when there is no unique valueMojtaba Faridzad19 Jun
    • Re: selecting PRIMARY KEY when there is no unique valueBruce Feist19 Jun
    • Re: selecting PRIMARY KEY when there is no unique valuePaul DuBois19 Jun
    • Re: selecting PRIMARY KEY when there is no unique valueDon Read19 Jun
  • Re: selecting PRIMARY KEY when there is no unique valuePeterWR19 Jun
  • Re: selecting PRIMARY KEY when there is no unique valueMichael Conlen19 Jun
  • Re: selecting PRIMARY KEY when there is no unique valueMojtaba Faridzad19 Jun
  • Re: selecting PRIMARY KEY when there is no unique valueKen Menzel19 Jun
  • how to limit COUNT(*)Mojtaba Faridzad22 Jul
  • Re: how to limit COUNT(*)gerald_clark22 Jul
  • Re: how to limit COUNT(*)Jerry22 Jul
  • Re: how to limit COUNT(*)Mojtaba Faridzad22 Jul
    • Re: how to limit COUNT(*)Fred van Engen22 Jul
  • Re: how to limit COUNT(*)Viorel Dragomir22 Jul
  • Re: how to limit COUNT(*)Mojtaba Faridzad22 Jul
  • Re: how to limit COUNT(*)Yves Goergen22 Jul
    • RE: how to limit COUNT(*)Mike Brum22 Jul
      • Re: how to limit COUNT(*)Mojtaba Faridzad22 Jul
  • Re: how to limit COUNT(*)gerald_clark22 Jul
    • Re: how to limit COUNT(*)Keith C. Ivey22 Jul
  • Re: how to limit COUNT(*)Mojtaba Faridzad22 Jul
    • Re: how to limit COUNT(*)Fred van Engen22 Jul
RE: Odd thingDallas Dickey19 Jun
RE: Odd thingJay Blanchard19 Jun
RE: Odd thingDave Christensen19 Jun
RE: selecting PRIMARY KEY when there is no unique valueMike Hillyer19 Jun
RE: Odd thingDave Christensen19 Jun
RE: Odd thingDave Christensen19 Jun
RE: Odd thingDave Christensen19 Jun
  • Re: Odd thinggerald_clark19 Jun
RE: Odd thingDave Christensen19 Jun