Hi,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'.
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?
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]
