I have a puzzle regarding database normalization. There are 2 tables BusinessSites and BusinessPeople. For each business site there may be multiple people and for each business person there may be multiple sites. Hence either table may be the parent or the child in a 1 to many relationship.

My problem is how to structure the fields for the connections. IE how would you tag the people so they could be associated with many sites? and vice versa? One thought is to have multiple site fields in the people table. (Site1, Site2, Site3, etc.) This strikes me as clumsy and difficult at best.

Another thought is to store the siteIDs in a text or memo field (BusinessPeople.sites) with values like (1,23,43,57) and then parse the field to identify the sites the person is associated with. I'm not really comfortable with this either.

The problem can't be really all that unusual so I was wondering how others deal with it.

Thanks