Help with referential integrity in relational database setup

[zeryx]

Hi all,
I'm returning to Microsoft Access after nearly 25 years, and while I used to be very comfortable with it (especially database normalisation!), I'm finding myself a bit rusty and unsure about my current setup.
I've created a relational database with the following tables:

• Executive Offices
• Offices
• Identified Critical Products
• Office Tools

The intended relationships are:

• One Executive Office can have many Offices.
• One Office can have many Critical Products and Tools.
• Each Critical Product or Tool can be used by multiple Offices.

The goal is to store details about Executive Offices, Offices, and the products/tools they use — and to be able to display which Office uses which Critical Product and which Tool.
However, I'm struggling to enforce referential integrity in the relationships, and I suspect my structure might not be correct. Could anyone advise on how best to model this setup, especially the many-to-many relationships between Offices and Products/Tools?


Thanks in advance!

[Welshgasman]

Might want to upload your DB?, as you have not even shown what you have already?
Even the Relationships window would be a start.

[zeryx]

Hi Welshgasman, thank you for your reply. I didn't upload my database as I wasn't aware it was possible. This is the relationships as they currently stand ... I've added a couple of tables to try and get my head round it, but have probably just confused matters even more!

[Welshgasman]

OK, with just a quick look, use the parent ID as the foreign key for the many table, NOT the actual name.
You need junction tables for many to many. What appears to be your junction tables (to the right of Offices, have incorrect joins on their right.

So will have as an example tblOfficeTool and tblCriticalProduct. You name them as you see fit, but I only ever use enough characters to make them unique.

It is a Royal PITA typing in Indentified_Office_Critical_Products_BIA all the time.
Do NOT have spaces in object names, makes typing so much harder again as you have to enclose withing square brackets. You have underscore in some fields and not in others. Learn to be consistent in your naming. Makes coding so much easier.

The Contingency measures might need to go into their own table. Most times when you have a repeating group, it should have it's own table. However I do not do that for addresses. The one time I break the rule.

Here is a pic of my Bibby Gazette DB. The links table, links Crew, Ship, Date and Rank.
Whilst I do not have or need junction tables, I am using ID fields. That links tables just holds those.

The naming is not that great as that was my first database. I also used Table Lookups which is not recommended, but I am aware of how they work.

[mike60smart]

Hi
Can you upload the actual database?

[CJ_London]

Each Critical Product or Tool can be used by multiple Offices.

you need to clarify this statement as it could indicate a one to many relationship or many to many

[zeryx]

Thank you, that was very helpful - and has resulted in a bit of restructuring! I'm know I've got it right as I can't get my relationships set up! I'll talk through this and hopefully it'll make sense.

1 Executive Office has many Offices
1 Office can have 1 BCP
1 Office can have 1 BIA
1 BCP can have 1 or more Critical Products
1 BIA can have 1 or more Critical Products
1 BCP Critical Product can have 1 or more Tools
1 BIA Critical Product can have 1 or more Tools
1 Tool have more than one Contingency Measure

I feel like my mind is getting boggled now and I very much appreciate help!

[mike60smart]

It would help if you upload a copy of the database with some data in each of the tables.

[mike60smart]

Hi
I believe the attached screenshot of your Relationships is nearer the mark.

It would help if you give us examples of the data in tblContingencies as well as the other 3 tables dealing with Measures.

[Welshgasman]

Upload an empty dB. Easier to modify.

[mike60smart]

The OP has contacted me and has settled on the attached relationships.

[zeryx]

I would like to say a MASSIVE thanks to Mike60Smart - your help has been invaluable!