Cemetery DB design Guidance

[mixup]

As always, I am back to this forum for guidance. I am working on a cemetary database and trying to figure out the best way to set this up. Here are the rules:
1. the Cemetery has several lots. Each lot has exactly 6 plots, and each plot has exactly two places.
2. Owners can by plots. One plot can have combined ownership by two or more owners and one owner can own more than one plot.
3. Places in the plots can be occupied or open. Occupied places have either cremations or full burials - Occupant information (different from owner) is also to be maintained.

I have designed a basic structure (SEE GRAPHIC) and now have the following questions:

1. Since Places can be Open or Occupied, I thought I would separate Occupants from the Place Table and have a 1to1 relationship (but I dont know how).

2. How do I ensure that each Lot has 6 and only 6 plots associated (1to6 relationship!) and that each Plot has 2 and only two places asccociated with it(1to2!)?

Any help/suggestion is appreciated.

Thanks

 

[Steve R.]

1. I would suggest creating a "serial number" for each plot. This will eliminate most of your tables. The "serial number" could be developed in this manner. Lot#Plot#Place#. Lots - would use numbers, Plots - the letters A-F, Places - either the number 1 or number 2. So you would have serial number that would look something like this: 123B1. That would uniquely identify each place. From there you would link the data tables that identify who is buried there and who owns the lot.

2. To identify if particular site is "open" or "occupied", just use a logic flag within whatever table you designate as your main table.

3. Should you use the serial number approach, do NOT use the autonumber field. The autonumber field should just be used as a "hidden" value for linking your data. The serial number would have its own field.

 

[mixup]

Thanks!

Thanks for your reply ortaias. I actually went ahead with my relationships there. (I had to retain the Place abd plots as numbers). I did limit the the options through a drop down (1-6 and 1-2). As far as the relationship goes, I managed to do a 1 to 1 by not allowing duplicates in the foreign key field. But generally, your response did make me feel that I was on the right track.

Thanks again.