Beginner’s Guide to Access Many-to-Many Relationships

ms access

I'm a beginner with MS Access hoping for some help with a database I am trying to create. This is (will be) a database of land parcels in a given area, and their associated landowners.

Here are my table fields, summarized in Excel (bold are my current PKs):

enter image description here

County Plat contains the parcels; the 'Owner' field is the listed owner for the land and could be an LLC, etc. so could have multiple individuals with varying stakes.

The Owners table associates these 'owners' with their corresponding landholders and their ownership percentage.

Finally, Contact List contains a given landholder's contact details.

So, for example, Parcel 0001 and Parcel 0009 are listed in County Plat as owned by Owner TOFU FAMILY TRUST, and the trust is listed in Owners as owned 50% by Jim Tofu and 50% by Bob Tofu. Their respective contact information is listed in Contact List.

Also, I would prefer not to edit the setup of the County Plat database if possible, as it comes from an outside source.

I would greatly appreciate any advice on how to best set up this database. I am currently getting errors trying to enforce referential integrity, and I'm pretty sure it is due to my lack of understanding of database design. Although I've read articles on it, I'm having difficulty applying those lessons to my own database. It would be really helpful to get some feedback from the pros on how to implement this.

Thank you!

Best Answer

Your design seems almost correct. I can pinpoint the following problems though:

You're using the table Owners for two purposes:

  • Have a list of owners (as entities/companies/societies)
  • Have a list of people with stakes in each entity

You need to break Owners in two, for example:

CREATE TABLE Owners (
  OwnerID int autoinc primar key,
  [Entity name] varchar(200)
);
CREATE TABLE Stakeholders (
  OwnerID int not null references Owners,
  PersonID int not null references "Contact List",
  [Ownership %] float not null,
  unique (OwnerID, PersonID)
);

The above requires you modify Contact List to include a PersonID int autoinc not null primary key, since you could have more than one person with the same name and surname.

Related Question