Database Design Part II

lefkogt

Thanks for all of your help, so far!

At http://www.gppbt.org/GPPBTDB.gif, is a relationship diagram (from Access -- but I'll be using MYSQL) of what I constructed to date for my requirements.

My only concern is the Birding Trips results. Is the Cross table correct? The following requirement is to be answered by it. "List birds seen on a birding trip at a particular site". There could be more than one trip date per site. Of course, you will see a different number of birds on any trip.

Thanks
Gary

eoghain

The way I'd handle your bird's sighted per trip problem is this. Since you already have a list of birds seen at a site you should use that XRef table to join to your trips table. Then if you see a new bird at a site that you hadn't seen before you can have your tool automatically add it to the bird_site_xref table and it'll be tracked properly. Your current setup allows for the siteing of a bird that you don't list as having been seen at a particular site. This can cause you inconsistent data, as well as more coding for the application to keep things straight.

So:

bird_site_trip_xref

ID
bird_site_xref_id
trip_id

That's the table I'd use.

lefkogt

Thanks for input.

My thoughts:
1. Birds Table contains all birds found in Colorado. This can be added to as new species are found. There are no dates associated with these birds. Think of it as a historical reference.

Sites Table contains all birding sites found in Colorado. This can be added to as new sites are found.
For Birding Trips, I need a date, what site visited, list o fbirds for that date (may or may not include some/all birds seen historically at that site).

For this portion I expect to answer:
1. What birds are in Colorado?
2. What birds are seen at a particular site?
3. For given site, list the trips by date and include the birds seen on that particular trip.

I'm still not confident my Trip requirements are being met as it is currently setup???

See updated Relationship Diagram:
http://www.gppbt.org/GPPBTDB.gif

Thanks
Gary

lefkogt

Thanks for input.

It seems to meet requirements:
1. List all birds.
2. List birds by site.
3. List birds by date by site.

See any potential problems?

See updated Relationship Diagram:
http://www.gppbt.org/GPPBTDB.gif

Thanks
Gary

mattr

I would drop those extranious ID fields everywhere. Bird_Site has a perfectly good primary key ( BirdID, SiteID )

As does birdsitetrip( BirdID, SiteID ) (add trip date unless you can explain why it needs antoher table?)

eoghain

I agree with Mattr, you should change your birdsitetripxref table to only hold it's own ID, the ID of a birdsitexref, and a birdingtribid. By using a birdid and siteid in this table you run into the potential to show that a bird was seen at a site on a particular trip, but if you search for that bird at that site it might not exist.

tomhath

I'd keep the ID fields, even if they aren't used today, the overhead is trivial and you might need them someday.

I'd keep the BirdingTrips table, someday you might want to add other information related to a trip. But add a second date so you have a From/Thru range instead of one row for each date unless you really need the discrete dates (Is it enough that a species was seen at a site between Aug 18 and Aug 20, or do you really need to know that it was seen on Aug 19?). Putting the date in the xref table denormalizes it badly.