List:General Discussion« Previous MessageNext Message »
From:Mark Goodge Date:July 16 2012 3:08pm
Subject:Unique index - opinions sought
View as plain text  
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 
(YYYY-MM-DD).

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?

Mark
-- 
  Sent from my Turing-Flowers Colossus
  http://mark.goodge.co.uk

Thread
Unique index - opinions soughtMark Goodge16 Jul
  • RE: Unique index - opinions soughtRick James16 Jul
    • Re: Unique index - opinions soughtMark Goodge16 Jul
      • RE: Unique index - opinions soughtRick James16 Jul