table Relationships

[jgnasser]

I am preparing a database for reporting wildlife incidents. Some of the data I will be entering in this database include the type of incident (there are basically three types we record) and the animal species involved (there are several species). Since the three incident types will be repeated several times, I thought I should just prepare a separate table with these and have it related to the main table and similarly for the species. One incident can involve several species just as one species can be involved in several incidents (many to many). I created a junction table to do this but the question is; How do I enter the data? Both in the incident table and the junction table? When it comes to using the data, do I have to pull the data from the other tables??

 

[FoFa]

Not really, you have a one to many, one incident, many species. The fact you have more than incident does not play into it. So what you really need is a table of Species per incident. Then you can tie the species to AN incident.
Species table would contain SpeciesID (and any other data related to the species)
Incident table would contain IncidentID (and any other data related to the Incident)
Species per incident table would contain (I would throw an autonumber in for good measure) and IncidentID (to tie back to an Incident) and a SpeciesID (to tie back to a species).
Species per incident then would contain all the species involved in an inicident (one to many).
TO query this information it would be something like:
SELECT I.IncidentID, S.speciesID
FROM IncidentTable I
Inner Join SpeciesPerIncident X on
X.IncidentID = I.IncidentID
Inner Join SpeciesTable S on
S.SpeciesID = X.SpeciesID

 

[Pat Hartman]

The relationship is many-to-many, Fofa; 1 Incident- many Species, 1 Species - Many Incidents.

See the many-to-many example that I posted.

Many-to-Many

 

[jgnasser]

When it comes to data entry, do I enter the data into my incidents table and then again into the juction table to specify which species the incident is associated with? If I am not recording any other info about the species in the Species table apart from IDs and names, does creating three tables still remain important?

 

[Pat Hartman]

The species table defines the species. The Incident table defines an incident. The junction table "links" the two tables. Normally the only columns in a junction table will be the primary keys of the tables that are being linked. If you examine the many-to-many sample db, you will see that in that sample, the junction table contains other columns. This is called intersection data. It is data that relates to or further defines the relation record. In your case, you would include date and place in the incident table. In the sample's case, date was used to further define the relationship since a company could book a venue multiple times, the relation's key needed to include date.

The bottom line is YES IT IS IMPORTANT TO CREATE ALL THREE TABLES.