Lots of Many-Many Tables

[vapid2323]

I wanted to see if I am doing things correctly or if there is a better way.

So I have a new database that I am creating but this particular database requires lots of Join tables because of Many to Many relationships I have had to setup.

My question is, would there be any issues combining all this into one table with a ton of FKs? Or am I stuck creating Join tables for each Many to many?

 

[vapid2323]

Just adding in a image to help figure out a better way... if there is one.

 

[gemma-the-husky]

I would have thought you could slim this all down

each vertical line could probably be managed in a single table


eg - you have a table for site staff, and another for auditors

just have 1 table - they are just people - and add a flag to identify the type of people.


ditto- for all the organisations on the rhs.


After you do this, you wont need so many junction tables. maybe you can use a single junction table, and an indicator identifying the type of link.


so you have

people(PersonID, status, other details)

organisation(OrgID, details, other stuff)

linktype(linkID, desc) - a new table

and then a

relationship table (relationID, personID, orrgId, linkId)


hope that idea helps.

 

[vapid2323]

I have removed the Auditor table and all the related join tables. I then added in the flags: Auditor/SiteStaff to the SiteStaff table.

Now I am looking at the other side of the image posted above, I dont know that I can join up the tables because I want to report on them as follows.

SiteHQ

Division

SubDivision

​

If I join them up I dont know that this would be possible anymore...But I am kinda new to Access so I would love to learn a new trick

 

[gemma-the-husky]

i suppose only you know how many tables you need

but it depends on the exact nature of the relationship

if a sub-division which to a division which belongs to a site HQ - then you could do this with 3 tables - but equally, you might do it with a single table, and just have a field idenitifying the unit which "owns" the entity. This works if really a HQ/Division/SubDiviison are really different categories of what is really the same thing - just a business entity. Bear in mind that you then need to store very little information about a site-HQ - because that information can be easily derived form collating the details from the lower level entities that form part of the grouping.


on that basis maybe the staff members/auditiors ought to be associated with a sub-division ONLY - since that necessarily implies they will be auditors all the way-up to the top level, if you will

now if they can audit arbitrarily at different levels then you may need a more complex structure.

that's what it all comes down to - the data analysis in the first instance.