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
Database Desing Opinions
Moderator: General Moderators
since you have a quite straight tree of dependencies and lots of changes I suggest you make strong use of normalization. In this case proably to the 3. form all way long