Design Issue: Resolving a many-to-many relationship

[mixup]

This is a design issue: I have a database with a PlotsTable and OwnersTable (table) in a many to many relationship, connected with a PlotOwnerLinkTable (that includes the PlotID and OwnerID Foreign keys). There is also an OwnerForm to allow adding owner details to the OwnerTable. The main form is based on the PlotsTable, where, I would like to include a list of owners as a subform (Tabular). For each new record added to the PlotTable, a list of (one or multiple) owners are to be added to the subform, limiting the owners to the records in the OwnersTable. If a new owner is to be associated with a plot record, the owner details must be added to the OwnersTable first.

One way to accomplish this is of course to disassociate the OwnersTable from the relationship and make it stand alone with an input form. Then create a PlotOwnerTable and link it to the PlotTable using the PlotOwnerLinkTable. The subform for the PlotTable could be based on the new PlotOwnerTable and the owner information can be looked up from the OwnersTable and then saved in the PlotownerTable. But that is probably not a good design since the same data is saved in multiple locations and updating one would not necessarily update the others.

I am sure there is a simple yet better way to deal with it where only the association(s) (between the plots and the owners) is(are) stored (probably through a query of some kind). I have not been able to think clearly to figure that out. Can anyone help me?

 

[KeithG]

Why not enforce referenctial integrity? This way no child record can exist if a parent does not exist.

 

[mixup]

I am enforcing referential integrity (for the PlotTable and PlotOwnerTable through the PlotOwnerLinkTable). But still I end up putting in the same information multiple times in the PlotOwnerTable that already exists in the OwnerTable. (I hope I am understanding you correctly).

 

[The_Doc_Man]

You have gone at least halfway along the correct path by recognizing the value of a junction table. (e.g. PlotOwnerLink)

What confuses me is that you suggest you need to enter data multiple times for one plot, many owners. This is an implementation error at best.

Enter owner data first. Enter plot data next. (or vice versa.) Only after both records exist, link them in a form with two combo-box drop-downs that let you select the owner and plot. A combo box can show you one value and store a different one. I.e. you can have the combo box display "Joe Jones" but store value 1234 as the owner ID.

If you have reason to believe that the owner and plot both exist, skip data entry to either table. Just call up the form dedicated to linking such that you can use the drop-downs to pick an owner and a plot.

Just off the top of my head, it would also seem, since plots (are we talking either housing or cemetery plots here?) pre-exist, you can enter their data ahead of time, sold or not.

Now... here is where YOU have to make a decision. If the above does not work for you because it doesn't allow you to capture something, you have a design problem in that the problem and your solution don't match.

There are a couple of exceptions...

Suppose (just for sh|ts and giggles) that owner A and owner B could each own 50% of plot 1. You COULD include the fraction in the junction table since it rightfully describes something that applies only to the combination of owner and plot. In cases like that, you haven' screwed up yet.

You COULD always say that all owners share equally. Again, nothing else to store and the rules are very simple. Though some folks might wish to own more than others depending on how much they paid.

With this layout, you can have multiple owners holding a share of a plot and one owner holding multiple plots and even multiple owners holding shares of multiple plots all itermingled.

Then the only trick is to assure that no one plot is owned more than 100% (unless of course you are emulating the movie The Producers...) That is a simple summation query of the junction table grouped by plot number.

Just to keep life simple, I would limit plot ownership to integer-expressed percentages. I.e. if three people shared a plot as closely as possible, one would hold 34% (the others 33% each) - OR - you would declare that remaining fractions are equally divided among ALL owners. That is probably easier to manage than SINGLE fractions for ownership. But whatever floats your boat is fine by me.

 

[CraigDolphin]

And if we're talking land plots....consider how to handle plot mergers, subdivisions etc that usually involve retiring a plot identification number and creation of new ones...

 

[The_Doc_Man]

Craig speaks truth.

Awful truth. (For this problem, awful truth.)

But truth nonetheless.

 

[mixup]

Thanks Doc Man abd Craig. We are talking cemetary plots here (So no splits/combinations etc. involved) and thankfully our clerk's office do not need to track ownership percentages. They just have to list the owners. While I will make it a requirement that the OwnerTable is filled out first, the same may not necessarily hold true for the PlotTable (and hence the PlotForm). I am not worried about the consistency of the Owner data. It is just that I was considering "normalization" (since I am still learning, I thought I would try to do it right rather than just make it work).

 

[mixup]

I think I have finally resolved it. I have the subform based on the relate table, which would only store OwnerID (through a list-limited combo-box)which would be a foreign key against the PlotOwnerLinkID, an autonumber. Also, I will have two unbound boxes in the subform that will display the Name and Address of the Owner based on the OwnerID in the subform (using DLookup). That way, I do not have to store the Owner Information multiple times, I just store the OwnerID. Thanks guys, the discussion just helped me think clearly.