Database Design help

[lefkogt]

Trying to figure out the most efficient way to design MYSQL for the following
requirements:

1. Colorado has over 400+ bird species. This list can increase at any time.
   The list sort order is by family name meaning that new bird species can be
   inserted at the beginning, end or middle (most likely of the three) of the
   database table.
2. We have over 1000+ birding sites. This list can be added to at anytime
   (once we have completed our birding trail, most likely not). From the master
   list (item 1) a subset list is created based on the individual site. Site 1 may
   121 birds, and site 2 may have 253 birds. This may change as new birds are
   identified at an individual site.
3. There is a subset list of the master list that interests birders from the
   east and west coasts. Let's say there are 50 birds that interest east coast
   birders and 35 birds that interest (not seen in their region) west coast birders
   -- this list can be added to at any time. Additional to the list needed, each
   bird (for the east and west coast birder) has a unique time of year it is here
   (Spring, Summer, Fall, Winter, Year Round), each bird has a probability of being
   seen (unlikely, probable, possible), and we have to be able to list these birds
   by site -- meaning Site A can have 7 birds for west coast birders and 12 birds
   for east coast birders to see that they wouldn't see in their region.
4. Must use the master list as a reference for creating site-specific birding
   trip checklists. For example, we take a trip to Site A and see 55 birds on
   22-June-2002 then at Site A we see 42 birds on a trip 21-July-2002.

Any advice welcomed!

Thanks
Gary

[eoghain]

From what I understand from your description you need a couple of tables to do this work.

1. Bird Family Names - this table will hold an id and a name of a bird family, if you were using a database that allowed for foreign keys it'd be linked to you birds table.

2. Birds - this table should hold all of the specific bird information that you can't spearate into another table (like family name). The items that you can separate should go into there own tables and be represented here by there unique id's.

3. Sites - this table should hold your site information. Things like location.

4. Birds by Site - this is a cross referance table that tells you what birsd have been seen at a particular site.

5. Birds by Site Probability - this is a list of probabilities of seeing a bird that has been spotted at a particular site during a given season.

6. Birds by Site by Date - this is another cross referance table that just holds the dates that any particular bird was seen at a particular site.

Now I've made some assumptions, like the east/west coast interest can be listed in the bird table since it won't change.

Table Definations
1. bird_families
bird_family_id int not null primary key auto_increment
family varchar(50)

2. birds
   bird_id int not null primary key auto_increment
   bird_family_id int not null
   name varchar(50)
   description varchar(4000)
   interest char(1)

3. sites
   site_id int not null primary key auto_increment
   name varchar(50)
   address1 varchar(50)
   address2 varchar(50)
   city varchar(50)
   state char(2)
   zip varchar(10)
   description varchar(4000)

4. birds_x_sites
   birds_x_site_id int not null primary key auto_increment
   bird_id int not null
   site_id int not null

5. birds_x_site_probabilities
   birds_x_site_probability_id int not null primary key auto_increment
   bird_x_site_id int not null
   probability int not null
   season varchar(20) not null

6. birds_x_site_x_dates
   bird_x_site_x_date_id int not null primary key auto_increment
   bird_x_site_id int not null
   date_seen date not null

This is a very basic design, but should give you an idea of what is needed. Let me know if you have any questions.

[tomhath]

Looks pretty good. The only thing I'd change is remove the "interest" column from birds and make an interest region table and another cross-reference table. That way you can accomodate as many parts of the country as you want (not just east and left coasts). Also have an interest level for someone from that region (high, medium, low, etc).

interest

interest_id not null primary key auto_increment
geographic_region varchar(50)

birds_x_interest

birds_x_interest int not null primary key auto_increment
bird_id int not null
interest_id int not null
interest_level varchar(10) not null

[eoghain]

Yeah, I didn't want to get too deep into it. That's why I did it that way.