I have a MySQL table (call it, say, item_spine) which contains three
fields which, together, form a unique key. These three fields are a
guid, a start date and an end date. The guid is alphanumeric (a
fixed-length six characters) and the dates are ISO format dates
I also have another table (item_detail) containing data which is keyed
to the first in that the unique key here is the unique key from
item_spine plus a line id (which is numeric).
At the moment, I simply have the three fields in the item_spine set as a
unique key, and replicate those three columns in item_detail and have
those plus line_id as the unique key, thus making a four-column key.
But, for performance reasons, I was wondering if it might make more
sense to create a single column in item_spine containing data which is
generated from the original three and use that as a unique key instead.
I then only need a single column in item_detail to link to item_spine,
and thus my unique key there can be only two columns.
Another option is to have an autoincrement column as a primary key for
item_spine, and then use that as the link key for item_detail. But I'd
prefer to avoid that, because the content of item_spine has to be
updated on a regular basis from external data and using autoincrement
means I can't do that using REPLACE INTO while still maintaining a key
association with item_detail.
Any thoughts? How would you do it?
Sent from my Turing-Flowers Colossus