Database Design help

[bunker]

We've recently found out my daughter has food allergies and have been told to stay away from any and all nuts (especially peanuts).

However, she still seems to be having reactions, mostly some swelling, to food, so my wife and I want to track the foods she's eaten when she has a reaction.

I'm not a complete idiot when it comes to databases, I'm a dba, however, we have a data architect at work that does all the designs, I just do installs, setup, security, backups, etc...

I couldn't design a database to save my ass (or my daughter's apparently)

Here's what I need and I think the many to many relationship is screwing me up.
Each incident will almost always have multiple foods.
Each food can, and probably will end up with, multiple incidents.

Table: Incident
Columns:
-IncidentID (Primary Key)
-Timestamp
-Reaction (swelling, rash, etc..)
-FoodID (Foreign Key?) (here's where my confusions kick in)

Table: Foods
Columns:
-FoodID (Primary Key)
-FoodName
-IncidentID (Foreign Key?)

I'm doing this in access, because I've got a touch of experience with vba and access forms, but could easily use sql express and throw together a classic asp front end (not a clue when it comes to .net).

Help me ATPF, you're my only hope. (The db architect at work is a grumpy bastard!)

 

[Crusty]

When you are doing a many-to-many relationship it's common to use a 3rd table to store the relations. So in your case, I would call the table Incident_Foods, with 2 fields, IncidentID, FoodID.

So when you go to report an incident that contained 3 foods, you would insert 3 records into Incident_Foods, all 3 would have the same IncidentID, but 3 different FoodID's.

Then it's just a few simple joins to either get all foods belonging to an incident, or all incidents where a food was involved.

 

[KLin]

There's no reason to have a foreign key for incidentID in the foods table. The Foods table is just going to store referential data. If you want an incident to have multiple foods associated with a single incident, you'll need an incident header(RecordID(autonumber), Date, Reaction, etc.) and an incident detail table (RecordID, IncidentID, foodID).

 

[bunker]

Thanks folks, that helps a ton.

 

[ICRS]

Originally posted by: KLin
There's no reason to have a foreign key for incidentID in the foods table. The Foods table is just going to store referential data. If you want an incident to have multiple foods associated with a single incident, you'll need an incident header(RecordID(autonumber), Date, Reaction, etc.) and an incident detail table (RecordID, IncidentID, foodID).

Shouldn't the Incident detail table include an incident detail ID field, some column that is unqiue in it. I have always been taught that the primary key field should always be a unique integer.

 

[KLin]

Originally posted by: ICRS

Originally posted by: KLin
There's no reason to have a foreign key for incidentID in the foods table. The Foods table is just going to store referential data. If you want an incident to have multiple foods associated with a single incident, you'll need an incident header(RecordID(autonumber), Date, Reaction, etc.) and an incident detail table (RecordID, IncidentID, foodID).

Shouldn't the Incident detail table include an incident detail ID field, some column that is unqiue in it. I have always been taught that the primary key field should always be a unique integer.

Which is why I mentioned the RecordID as a field for the detail table. I would make that the PK and also an autonumber(or identity field in MSSQL).

 

[clamum]

Originally posted by: Crusty
When you are doing a many-to-many relationship it's common to use a 3rd table to store the relations. So in your case, I would call the table Incident_Foods, with 2 fields, IncidentID, FoodID.

So when you go to report an incident that contained 3 foods, you would insert 3 records into Incident_Foods, all 3 would have the same IncidentID, but 3 different FoodID's.

Then it's just a few simple joins to either get all foods belonging to an incident, or all incidents where a food was involved.

This is what I'd do as well.

Tables:

Incidents
- IncidentID (primary key)
- Timestamp
- Reaction

Foods
- FoodID (primary key)
- FoodName

Incidents_Foods
- RecordID (is this needed, for a primary key? I usually add this column but it's probably not necessary)
- IncidentID
- FoodID

You could have a form that has a textbox for Reaction and a textbox for FoodName. The insert would do: "INSERT INTO Incidents (Timestamp, Reaction) VALUES (CURRDATE(), 'Reaction text here')" and then "INSERT INTO Foods (FoodName) VALUES ('FoodName text here')". Have those insert statements return the IncidentID and FoodID, respectively. Then "INSERT INTO Incidents_Foods (IncidentID, FoodID) VALUES ('IncidentID', 'FoodID')".

That's assuming you have some sort of web interface to enter values. If you're using something like SQL Management Studio or phpMyAdmin then you wouldn't return any IncidentID or FoodID, just take note of the autonumber that is used.

 

[Crusty]

Originally posted by: clamum

Originally posted by: Crusty
When you are doing a many-to-many relationship it's common to use a 3rd table to store the relations. So in your case, I would call the table Incident_Foods, with 2 fields, IncidentID, FoodID.

So when you go to report an incident that contained 3 foods, you would insert 3 records into Incident_Foods, all 3 would have the same IncidentID, but 3 different FoodID's.

Then it's just a few simple joins to either get all foods belonging to an incident, or all incidents where a food was involved.

This is what I'd do as well.

Tables:

Incidents
- IncidentID (primary key)
- Timestamp
- Reaction

Foods
- FoodID (primary key)
- FoodName

Incidents_Foods
- RecordID (is this needed, for a primary key? I usually add this column but it's probably not necessary)
- IncidentID
- FoodID

I would say it's not needed if this is all the application is going to be, but it certainly won't hurt in this case. This would allow you to easily create another table to store ancillary data about this particular Incident/Food relation or even use the same table to store the data.