Thanks a lot. after all of these helpful responses, I will add a new field for Primary key.
Thanks again ----- Original Message ----- From: "Michael Conlen" <[EMAIL PROTECTED]> To: "Mojtaba Faridzad" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, June 19, 2003 11:43 AM Subject: Re: selecting PRIMARY KEY when there is no unique value > 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 > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
