How would you choose keys for the following?
Posted by: Giles Jonson
Date: June 11, 2007 06:16AM

Hi

I have a query about choice of keys and table setup in the following case, and wondered on peoples thoughts. It boils down to whether to introduce an artificial auto_increment primary key when other candidates for a pimary key exist, and that whilst longer, might reduce the number of indices that are used. I'll assume MyISAM so that auto increment is not limited just to the first part of the primary key, although Inno may prove a better choice in time and so require the auto_increment to be the first key column.

We'll use 3 tables, Owners, Pets and Diseases. Each owner may have multiple pets, and each pet may have multiple diseases. As well as looking up details based on owner, we may want to identify owners who had owned pets with particular diseases.

Using simplified tables, and leaving aside types, best field sizes and some other indices that we may need, one choice could be:

OWNERS:
owner_id auto_increment
(name, address etc.)
primary key (owner_id)

PETS:
owner_id
pet_id auto_increment
(name,type etc.)
primary key (owner_id,pet_id)

DISEASES
owner_id
pet_id
disease_id auto_increment
(disease type, details etc.)
primary key (owner_id,pet_id,disease_id)

With this, we can easily join to select all details for an owner, given an owner ID find pet and disease details, and just diseases if we wanted. We can also identify owners having pets with certain diseases without joining on the pets table. This seems fine, although the keys get increasingly long and PETS and DISEASES would not work with Inno.

So instead, would the following be better?

Keeping OWNERS as is:
PETS
pet_id auto_increment
owner_id
(name,type etc.)
primary key (pet_id)
index on (owner_id)

DISEASES
disease_id auto_increment
pet_id
owner_id
(disease type, details etc.)
primary key (disease_id)
index on (pet_id)

The primary keys will now be marginally shorter, but we now have an extra index on PETS and DISEASES, and if we do consider type sizes, the pet_id and disease_id fields will also now need to be larger. Having owner_id in DISEASES is now not essential, but does avoid a join on PETS to go from DISEASES to OWNER. If we did want to access diseases for a given owner, we would also need to join on PETS having no index on owner_id, or add a third index to DISEASES on owner_id.

My feeling now is that on balance, the second design may be preferable as it does alllow a switch to Inno, and whilst there are additional indices, they are shorter. I'd welcome other views though.

Giles

Options: ReplyQuote


Subject
Written By
Posted
How would you choose keys for the following?
June 11, 2007 06:16AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.