MySQL Lists are EOL. Please join:

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

>Hi,
>
>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?
>
I always use a id field with auto increment. It helps for normalization, 
and makes the code I use to deal with information very generic, grated 
I've abstracted the code to the point that it has no clue what it's 
doing, it just gets it done. In my case, I know that the foreign key is 
always one column and I can short cut the lookup to create the joins, 
it's "it's an index, it's a foreign key, it's this table and index." If 
the foreign key's index could be anything then It's "it's an index, it's 
a foreign key, it's this table and index, the index are these columns" 
and the code to generate the join is 'interesting'.

The other issue is that while your timestamp should be unique when 
combined with an invoice by whatever rules your dealing with, there's 
nothing that says it will be in the real world (the one where crazy 
things happen). By having the id field I never, ever deal with it 
myself, MySQL always puts the number in there for me and I know it's 
going to be unique unless MySQL does something it should not do.

The id field just takes the guesswork, mess and headaches out of the 
code (well not *all* of them, but enough) and with the size of disk 
space these days the extra space isn't much.

--
Michael Conlen


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