Solved Database Relationship (1 Viewer)

AC123

New member
Local time
Today, 06:48
Joined
Apr 22, 2020
Messages
8
Good Evening All,

I apologise I am very new to the access world and I am having issues relating relationships within my database and would appreciate any help you could provide.

Please see the relationship format for my db below. I am having issues relating the table surveys to the customers/department/rooms etc.

DBRelationship.png


Each survey has one customer however that survey can have multiple premises/departments/rooms. However dosemeter number in tblsurveyresults can only have one customer/department/room.

Initially I set the relationship as shown below however I feel like the relationships are too complicated and does not ensure data integrity?

DBRelationship2.png
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:48
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

For us to verify the table relationships, we'll need to understand the business process you're trying to model. Can you give us a brief or detailed description of what your database is used for? Thanks!
 

CJ_London

Super Moderator
Staff member
Local time
Today, 06:48
Joined
Feb 19, 2013
Messages
16,553
suspect you don't need the roomname or departmentname table, just include the name in the room or department table

think your dosemeter results table also needs more fields - e.g. result, date of result and perhaps saturation period (although perhaps calculated by deducting the survey date from the result date).

your initial design looks OK in principle, but does not need the relationships between survey and premises and departments since these can be determined by the relationship with room

with regards the survey then if rooms are subject to multiple surveys, you need a roomFK field in the surveyresults table (and not a surveyFK in the room table). Also suspect you do need a customerFK in the survey table - but as DBG says, you need to explain your process.

If this is what I think it is I did something similar many years ago for a company that put argon dosemeters in place, primarily for large landlords but also the general public. Primary issues were around actually tracking dosemeters to the right location, ensuring they were returned to a schedule etc. If this is similar, you may want additional information
 

AC123

New member
Local time
Today, 06:48
Joined
Apr 22, 2020
Messages
8
Hi! thank you both for replying.

For us to verify the table relationships, we'll need to understand the business process you're trying to model. Can you give us a brief or detailed description of what your database is used for? Thanks!
I work for a radiation physics company in which we monitor the dose of radiation in a particular room, routinely every 3 years.
We have customers that have individual premises, departments and rooms.

suspect you don't need the roomname or departmentname table, just include the name in the room or department table
These departments may be 'Radiology' or 'X-Ray' and the rooms may be 'Room 1', 'Surgery 1' hence why I have done a tblDepartmentName and tblRoomName table to standardise these lists. This is incase I wanted to do a search in the future that would look at all dose's in 'Radiology' across all customers and wouldn't have spelling mistakes. However I have linked the premises to the department as not every premises has the a department of 'Radiology' and is specific that that premises, if I am making sense?

I plan on adding more fields to tblSurveyResults I just didn't want to over complicate it so early.

with regards the survey then if rooms are subject to multiple surveys, you need a roomFK field in the surveyresults table (and not a surveyFK in the room table). Also suspect you do need a customerFK in the survey table - but as DBG says, you need to explain your process.
Yes this makes sense, thank you!
 

Users who are viewing this thread

Top Bottom