Table Layout/Normalization

[acmumph]

Recently got involved with local 4-H and wanted to improve animal/project tracking. I thought PHP/MYSQL would be just the thing to do it. With that, I've come up with the following table layout.

 

exhibitor (exhibitor_id(P), exhibitor_firstname, exhibitor_lastname, exhibitor_phone,exhibitor_street, exhibitor_city, exhibitor_state, exhibitor_zip, exhibitor_organization,

    exhibitor_points, animal_id)

show (show_id(P), show_name, show_date, winners_id)

animal (animal_id(P), animal_species, animal_tag, animal_weight, show_id)

winners (winners_id(P), winners_place, winners_class, winners_grand, winners_reserve ,animal_id)

 

Rationale:

1. Each exhibitor can have multiple animals

2. Each animal can be in multiple shows but only has one exhibitor

3. Each show has multiple winners

 

Question:

  How far off am I in establishing relationships between the tables? the first two rationales seem pretty straight forward, but the 3rd rationale (winners) is a little confusing for me...Appreciate any insight/info you could provide.

 

Thanks,

Aaron

[ignace]

1. Each exhibitor can have multiple animals

 

You have turned this relation the other way around as it should be:

 

exhibitor (exhibitor_id, ..)
animal (animal_id, exhibitor_id)

 

2. Each animal can be in multiple shows but only has one exhibitor

 

I assume each show also has multiple animals:

 

animal (animal_id, ..)
show (show_id, ..)
animal_show (animal_id, show_id)

 

both keys in animal_show are primary, that is, assuming each show is present only once in the shows table (on a different date) otherwise you'll have to add a date column to the table as part of the primary key.

 

3. Each show has multiple winners

 

Same as with 2 (same assumptions)

 

show (show_id)
winner (winner_id)
show_winner (show_id, winner_id)

[acmumph]

Appreciate the info. You are correct in your assumptions. When you say

both keys in animal_show are primary, that is, assuming each show is present only once in the shows table (on a different date) otherwise you'll have to add a date column to the table as part of the primary key.

I assume you mean they are Primary Keys in their respective table, correct?

 

I'm trying to visualize the data input process and where/how the animal_show and show_winners table will be populated.

My confusion is on the show_winners(show_id and winners_id) If those two are auto_incremented from their respective table, how do those auto_increment values get to the animal_show table?

 

Below are the tables I created and am going to start throwing sample data into them to see if I can come up with some possible scenarios on extracting specific bits of info....(i.e. which exhibitor has most accumulated points and list the shows they won, etc...)

CREATE TABLE `animal` ( `animal_id` int( 11 ) NOT NULL AUTO_INCREMENT ,
`animal_species` varchar( 50 ) NOT NULL COMMENT 'Wether, Angus, Brahma',
`animal_tag` varchar( 20 ) NOT NULL COMMENT 'County Tag',
`animal_weight` int( 5 ) NOT NULL ,
`exhibitor_id` int( 11 ) NOT NULL ,
PRIMARY KEY ( `animal_id` ) ) ENGINE = MyISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT =1;
CREATE TABLE `animal_show` ( `animal_id` int( 11 ) NOT NULL ,
`show_id` int( 11 ) NOT NULL ) ENGINE = MyISAM DEFAULT CHARSET = latin1;
CREATE TABLE `exhibitor` ( `exhibitor_id` int( 11 ) NOT NULL AUTO_INCREMENT ,
`exhibitor_firstname` varchar( 50 ) NOT NULL ,
`exhibitor_lastname` varchar( 50 ) NOT NULL ,
`exhibitor_phone` varchar( 12 ) NOT NULL ,
`exhibitor_street` varchar( 60 ) NOT NULL ,
`exhibitor_city` varchar( 50 ) NOT NULL ,
`exhibitor_state` varchar( 2 ) NOT NULL ,
`exhibitor_zip` varchar( 12 ) NOT NULL ,
`exhibitor_organization` varchar( 70 ) NOT NULL ,
`exhibitor_points` int( 5 ) NOT NULL ,
PRIMARY KEY ( `exhibitor_id` ) ) ENGINE = MyISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT =1;
CREATE TABLE `show` ( `show_id` int( 11 ) NOT NULL AUTO_INCREMENT ,
`show_name` varchar( 70 ) NOT NULL COMMENT 'Wether, Angus, Brahma',
`animal_tag` varchar( 20 ) NOT NULL COMMENT 'County Tag',
`show_date` DATE NOT NULL ,
PRIMARY KEY ( `show_id` ) ) ENGINE = MyISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT =1;
CREATE TABLE `show_winners` ( `show_id` int( 11 ) NOT NULL ,
`winners_id` int( 11 ) NOT NULL ) ENGINE = MyISAM DEFAULT CHARSET = latin1;
CREATE TABLE `winners` ( `winners_id` int( 11 ) NOT NULL AUTO_INCREMENT ,
`winners_ring` varchar( 4 ) NOT NULL ,
`winners_place` varchar( 6 ) NOT NULL ,
`winners_class` varchar( 4 ) NOT NULL ,
`winners_grand` tinyint( 1 ) NOT NULL ,
`winners_reserve` tinyint( 1 ) NOT NULL ,
`animal_id` int( 11 ) NOT NULL ,
PRIMARY KEY ( `winners_id` ) ) ENGINE = MyISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT =1;

 

 

Thanks again

 

 

[ignace]

No with both keys are primary I really meant both keys are primary. Primary has nothing to do with whether they auto_increment or not.

 

CREATE TABLE animal_show (
  animal_id INT UNSIGNED NOT NULL,
  show_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (animal_id, show_id)
);