Suggestions for Design
Posted: 2016-04-06 13:13
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;
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;
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!
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 2503 times
- persons_table_valuation_tabs.PNG (40.76 KiB) Viewed 2503 times
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!