Compliance Database design in Access 2019 (1 Viewer)

picasso87 · Apr 16, 2019

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 · Apr 16, 2019

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 · Apr 16, 2019

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.

picasso87 · Apr 17, 2019

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 · Apr 17, 2019

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

mike60smart · Apr 17, 2019

Hi

I would recommend setting Referential Integrity between all tables.

Pat Hartman · Apr 18, 2019

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.

picasso87 · Apr 18, 2019

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

Cronk · Apr 18, 2019

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.

picasso87 · Apr 19, 2019

Hi Cronk,

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

Cronk · Apr 19, 2019

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

The_Doc_Man · Apr 19, 2019

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.

Compliance Database design in Access 2019 (1 Viewer)

picasso87

New member

Attachments

jdraw

Super Moderator

plog

Banishment Pending

picasso87

New member

Attachments

plog

Banishment Pending

mike60smart

Registered User.

Pat Hartman

Super Moderator

picasso87

New member

Cronk

Registered User.

picasso87

New member

Cronk

Registered User.

The_Doc_Man

Immoderate Moderator

Similar threads

Users who are viewing this thread