>>I have a legacy database full of compound keys which I'm renovating. It has a couple of tables; let's call them Things and Locations, linked by a compound key consisting of two or three fields. Many Things can have the same Location, and a Thing can have many Locations. Reports can order things by Thing or Location. It looks like a many-to-many situation, except for one thing: there are many fields characteristic of Things, but the only field that characterizes a particular Location is the Location number. Therefore, if I make a many-to-many system, with two parents joined by a child, the Location parent will have nothing but a Location number field and maybe a surrogate key. So I am not sure if I gain anything by having a Location parent table. Many of the Location fields repeat a lot, but none of them repeats consistently with Location number or Thing key. So I think I will make the Locations children of the Things and give them Thing parent keys, but no keys of their own.
>>To make the common but inconsistent duplication less of a chore, I plan to make the Add Location code give the option of copying an existing Location for modification.
>>
>>Comments, anyone?
>
>A Thing can be in more than 1 location? Like copies of a document/book? No matter what you still have a many-to-many relationship. I have seen systems where the database designers used an intermediate table between the Things and Locations. This would make the Locations table smaller (only 1 occurrence of a location). The intermediate table goes both ways (from Locations to Things and Things to Locations). There is a row in Intermediate for each location every Thing has. You can then link from any Location through the intermediate table to select all Things at that location. If you were to conceptualize/visualize [such language! :) ] this on paper it is much easier to figure out than explain.
>
>I would also restrict who can add locations. People get lazy looking up what is already there and just stuff in their own interpretation of a location description.

Bret, we did a similar design (people and activities) and settled on an intermediate table with 3 fields: UniqueID, PersonID and ActivityID. We had 2 paramaterized views, one showed everyone at a specific activity and the other all the activities one person had attended. It's working fine, with about 100,000 entries and 6 people using it.

You might have to add a couple of extra fields to take care of the compound keys - or you might concatenate them in the intermediate table.

HTH
Barbara