>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

Any time there is a "many-to-many" relationship between two tables, a third table is required to resolve this into to "one-to-many" relationships. Just as in the example Hugo gave.

Here is an other, quite common, example: Invoices vs. products. Each invoice can have several products, each product appears in several invoices. This many-to-many relationship is commonly resolved by a separate table for the invoice details.