Relationship with a Many-to-Many Relationship

[haydenbottoms]

I have created a many-to-many relationship but am now having trouble figuring out how to add new data.

I have a table PEOPLE:
ID First Name Last Name
1 Participant A
2 Participant B
3 Participant C

And I have a table BEHAVIORS:
ID Behavior
1 Yelling
2 Grabbing
3 Smearing feces

And I linked these together in a many-to-many relationship in a table PEOPLE_BEHAVIORS:
ID Participant Behavior
1 A Yelling
2 B Yelling
3 C Yelling
4 A Grabbing
5 B Grabbing
6 A Smearing feces
7 C Smearing feces

All that’s good to go. But now I need a table so I can count how often these behaviors occur every day. I need to link PEOPLE_BEHAVIORS to a new data table that will count how often I see the behaviors linked to the people every day. So my table should look like:
ID Participant Behavior Date Frequency


But I should only document the frequency of the behaviors linked to that participant (so for Participant A, I should have a space for yelling, grabbing, and smearing feces whereas for Participant B, I’d only have a space for yelling and grabbing). Is it possible to create a table that will basically partially create records for me? So if I open the table to add data today, I’d see the following and fill in the frequency blanks?
ID Participant Behavior Date Frequency
1 A Yelling 6/16/14
2 B Yelling 6/16/14
3 C Yelling 6/16/14
4 A Grabbing 6/16/14
5 B Grabbing 6/16/14
6 A Smearing feces 6/16/14
7 C Smearing feces 6/16/14

 

[plog]

First before I tear you to shreds and leaving you crying, let me commend you on reading up on normalization. You structure shows you took the time to learn about properly structuring your data and building the necessary tables. This puts you ahead of a lot of people and you shouldn't have any trouble getting where you want to go.

You just missed it by a little though. PEOPLE and BEHAVIORS are correctly structured. If I had to pick nits, I'd say your field names shouldn't contain non-alphanumeric characters (this means no spaces: 'First Name' should be 'FirstName'). It will make coding and writing queries easier down the line. Also, I'd prefix each ID field with what its for (i.e. People_ID, Behavior_ID). When you start joining tables and you have 5 fields all named ID it will get confusing.

Where you went wrong was in PEOPLE_BEHAVIORS. This is called a junction table (http://en.wikipedia.org/wiki/Junction_table) and is used to define many-many relationships. So far, so good, you're doing it right. However, what you did wrong was not use the ID fields from the 2 tables it is joining. Instead of text data, PEOPLE_BEHAVIORS should only contain 3 numeric fields. Using your sample data it should look like this:

PEOPLE_BEHAVIORS
PeopleBehavior_ID, PeopleID, BehaviorID
1, 1, 1
2, 2, 1
3, 3, 1
4, 1, 2
5, 2, 2
6, 1, 3
7, 3, 3


That does the same thing yours does, but it properly uses the ID field you created for each Person and Behavior--this is why you use those ID numbers.

Now, for the last table you reference the BehaviorID from PEOPLE_BEHAVIORS and that's it:

FREQUENCY
FrequencyID, BehaviorID, FrequencyDate
1, 1, 6/16/2014
2, 2, 6/16/2014
...

You don't need Participant or Behavior data in this table because you will retrieve it by using the BehaviorID.

Let's step through that last set of data verbally: On 6/16/2014 PeopleBehavior_ID 1 occured. So you go to PEOPLE_BEHAVIORS and see that PeopleBehavior_ID involves PeopleID 1 doing BehaviorID 1, next you go to PEOPLE to find out who PeopleID 1 is and to the BEHAVIOR table to find out what BehaviorID 1 is. From all of that you can determine that Participant A Yelled on 6/16/2014. That's how it should be set up.