many to many resolution

[wally buch]

I am working on a clinical trial database. There are four tables. Two of them are TrialTitleID and another is InstitutionNameID and both will have primary keys. But these two tables are many to many, i.e. one institution can have many trials and a trial can have more than one institution that runs the trial. I can't figure out a logical way to have an intersection table to make these each be one to many.

Any help is greatly appreciated.

[Fitch]

You sort of answered your own question: create a ("join") table that contains at a minimum the Trial ID and the Institution ID. Each record in this table links to a trial on one side and an institution on the other.

[wally buch]

thanks for the help on the join table. What is confusing me is what else goes into this table? Is it just the references to the other tables or would there be other fields required. The table schema I have worked out is as follows. Study Title - institution name - Doctor name - patient name.

One study can be to many institutions, and one institution can have many studies. One institution can have many doctors and one doctor can have many patients. I am not sure how to diagram this on the ERD.

Thanks in advance

[bcooney]

A join table resolves the many-to-many relationship between two tables. Often, more data is required about this union, and so you'll add more fields.

For example, in a system I developed people are assigned to sites. Sites have many people, people are assigned to more than one site. I have the tables: People, Sites and PeoSites (join). However, each person is assigned a Role that is unique to their assignment at the Site. Therefore, I have a RoleID in the PeoSites table.

One thing that you must understanding is that all tables should be related by IDs, not text values such as names.

[comment]

See if this helps:

http://www.fmforums.com/forum/showpost.php?post/246136/

[Fitch]

Generally the join table will have very few fields. Barbara gave a good example of the "role" as an additional type of field you might store in the join table.

This is how I would diagram the structure as described:

Study >- Join* -< Institution -< Doctor -< Patient

*(The join table can be named anything you like.)

Might it also be the case that doctors work for more than one institution, and patients may see more than one doctor? If so, add a join table between each of those tables too.

Lastly, keep in mind that the FileMaker relationship graph is not an ERD, even though it resembles one on the surface.