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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
