Two relationship problems

[niktemadur]

Hello there. I'm stuck and I hope somebody can help me out a bit, I've isolated my predicament in a zip file. To protect the innocent, all names are of baseball pitchers from the sixties. Here goes:

Let's say I've got three Departments (offices, warehouse and workshop), each Department must create security Teams (fire, first aid and evacuation), each Team has several specific Functions (they're all in the database, have a looksee). I'd like to keep all Team members in one field, instead of splitting them all into separate fields for each Team, if at all possible. But since some employees belong to more than one team, repeating fields seem like a logical way to handle it.

In the first file (Employees.fp7), the first Brigade and Function lines work fine, it's in the second line onward where things go haywire.

Then, in the other file (Functions.fp7), the same and only one employee shows up in every Team and Function of his respective Department.

Please check out the file, I'm sure it makes more sense than my written description. Can somebody please help?

FM_Forums.zip

[comment]

I don't understand your files at all. Based on your description, I think you need a structure like the one depicted in the attached ERD.

since some employees belong to more than one team, repeating fields seem like a logical way to handle it.

No, the logical (and correct) way is to use a join table.

[comment]

Here's a more elaborate schema that would allow Assignments to use a value lists of available functions, according to the selected team's type.