Compliance Database design in Access 2019 (1 Viewer)

[Deleted member 148129]

Hi Experts! Hope you will be able to help me?
I am building a database to record our responsibility for various compliance types (water, fire and asbestos risk in properties). If we are responsible, we do inspections, otherwise we just keep the data about client units so they appear on report for the client. My problem is with the design as i have repeatable foreign keys in 3 tables. Attached is a screenshot of my database. Can you tell me how to design this differently to avoid repeatable fields?

 

[jdraw]

You may get some ideas from this free model from Database Answers.org.

A quick look at your png suggests you may not have a table for Findings(inspection result/issue(s)) to which you could identify remedial activities. It would help readers, and could offer you more focused responses, if you would provide a description of the business processes involved in your compliance reviews/inspections and assessments in simple terms --no jargon, no quasi-database.
Good luck.

 

[plog]

It's up to you to make that decision since you understand your work better than us. But we can guide you there.

My advice is to start a new database and copy your tables into it and build a new relationship tool. Do it table by table, starting with whatever table you deem your "main" table. Then from there bring in just one table you think is directly related to it. Join them appropriately and then add the next table.

When you come to a table you are uncertain of or want to add to multiple tables ask yourself what it really is apart of. Are Responsibilites part of ClientUnits or ComplianceTypes?

When that doesn't help, post back here a definition of what each is and how the data in each relevant table is to operate so we can understand and guide.

 

[Deleted member 148129]

Thank you for your advice, I have now revised the schema and this is what i have got. The biggest issue here is that I am forced to use unique identifier of junction table (ComplianceUnitResponsibility) as foreign key in other tables (Inspections and Remedials). Is this an issue? Can this be avoided? Inspections and Remedials are related to both units and compliance types based on responsibility. Hope this makes sense? Let me know if not. Thanks again for your help.

 

[plog]

That looks good. If Inspections and Remedials are tied to a unique Unit/Compliance permutation then you have set this up correctly.

 

[mike60smart]

Hi

I would recommend setting Referential Integrity between all tables.

 

[Pat Hartman]

In addition to the RI, you need to create a unique index on UnitID and ComplianceTypeID to ensure no duplicates can get added to ComplianceUnitResponsibility. You will need to use the Indexes dialog since you cannot create multi-field uniuqe indexes field by field in the table itself. That method only creates single field indexes.

To create a multi-field index,
Add a name, pick the first field, and select the unique option.
On the next row, leave the name field blank and pick the second field.
You only need two fields in this case but Access supports a max of 10 fields in any single compound index.

 

[Deleted member 148129]

Thank you all for your help. It is truly awesome and highly appreciated.

 

[Cronk]

In your relationships screen, add another 2 instances of the table ComplianceType. These will have _1 and _2 as suffixes. Then link only one of each of the 3 tables to the respective join tables.

 

[Deleted member 148129]

Hi Cronk,

What is a purpose of having duplicates of ComplianceType table showing on relationship screen?

 

[Cronk]

Um, to fix the problem in #1 of this thread.

 

[The_Doc_Man]

Note that the duplication in the Relationships table is a duplicate POINTER, not a duplicate table. Having this kind of duplicate is most OFTEN related to self-referential tables, but there can be other situations where a duplicated reference is appropriate.