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?