Solved Database Relationship

[AC123]

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.

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?

 

[theDBguy]

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]

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]

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!