Suggestions for Design

[TheNoLifer]

All, wondering if any of you could give me some suggestions on how to handle a new requirement?

I have a Persons table - people's names, dates of birth, etc.
I have a Locations table - places, addresses, etc.

I am now adding a table to capture Valuation Roll data. This is data going back to the 1880s of lands in Scotland, the owner and the value - recorded for taxation purposes.

The original data is arranged in columns as follows;

PLACE | PROPRIETOR (OWNER) | OCCUPIER | TENANT | INHABITANT | VALUE

In the data, the same person can be the Proprietor and also the Occupier, or there can be Person A as Proprietor, Person B as Tenant, and then Person C as Inhabitant.

I created a ValuationRoll table with multiple lookup fields back to the Persons table and achieved this;

valuation_ada.PNG (63.95 KiB) Viewed 2499 times

The problem with this is that by going to the Persons table and looking up one of the people referenced in the Valuations table - say the Bannerman guy, I get;

persons_table_valuation_tabs.PNG (40.76 KiB) Viewed 2499 times

Which I guess makes "sense" since I'm joining to that table four times. But it's clunky and doesn't really make sense to the user.

I could hide all these tabs - but that defeats the purpose a little - we want to show where a person has an associated Valuation Roll record.

I tried having one Person lookup and a drop-down for "Owner/Occupier, Occupier, Tenant, Inhabitant, etc" and then just splitting the data into multiple rows - one row per person, but then what happens to the values? The value is at the "Place" level and I didn't want it repeated X times.

Anyone have any thoughts on how I could deal with this data in a sensible way?

Thank you!

[TheNoLifer]

Never mind! I've gone with the 1 record per person option and will train my users only to add a value for the "Proprietor/Owner" record.

new_1855_valuation_roll_method.PNG (70.33 KiB) Viewed 2492 times

Would still be interested to hear any thoughts about shaping data such as this into a model.

Thanks,

A.