Are my table relationships ok? Pic attached

[AccessNooBie]

My database is to track customer incidents. Can anyone tell me if my relationships look ok? I'm a remedial access user to any input would be appreciated. Thanks in advance for the help.

 

[pono1]

Pretty good. Looks like you put some work into this one. But without knowing the requirements I can only offer up some questions based on a quick look. The questions may or may not be relevant...

Are you sure you need two employee tables? Do you have enough data fields in the employee table? Is it possible someone might ask how many different employees have worked on an incident over its life? That is, I am assuming an incident might be assigned to one employee initially but later re-assigned to another employee. Do you need to track the date an incident was closed and then perhaps in the future create a report to show the average length of time incidents are open? The three description fields in the status table look like repeating groups -- if so you may need another table to represent the one-to-many relationship...

Regards,
Tim

 

[AccessNooBie]

Pretty good. Looks like you put some work into this one. But without knowing the requirements I can only offer up some questions based on a quick look. The questions may or may not be relevant...

Are you sure you need two employee tables? Do you have enough data fields in the employee table? Is it possible someone might ask how many different employees have worked on an incident over its life? That is, I am assuming an incident might be assigned to one employee initially but later re-assigned to another employee. Do you need to track the date an incident was closed and then perhaps in the future create a report to show the average length of time incidents are open? The three description fields in the status table look like repeating groups -- if so you may need another table to represent the one-to-many relationship...

Regards,
Tim

Thanks for taking the time to respond. I'll answer your questions and then maybe you'll have more input?

1. You mentioned repeating groups in the status table. Did you mean the parts table? I do have a repeating group in that table; unfortunately our accounting software stores the part desctriptions in three separate fields, so when I exported the parts list that is how it came out. Rather than worrying about it, I just left it. Should I reconsider this?

2. The second employee table is an alias table (tblEmployees_1); does this count as a second table, and should it be removed?

3. The employee table currently has very few fields. The main purpose of the employee table is to show which employee either opened or closed the incident, so i'm not too worried about populating more fields with employee information...but that could change in the future.

4. Currently we're not too concerned with reassigning an incident to different employees, so an extra table to facilitate the many to many relationship doesnt seem to be a requirement (at least not right now)

5. I do want to track the date an incident was closed and then create a report to show the average length of time incidents are open. I do have a DateClosed field, but its not shown on the PDF I posted.

Thanks again for your input! It's much appreciated.

 

[pono1]

You mentioned repeating groups in the status table. Did you mean the parts table? Should I reconsider this?

Without knowing how those description fields will be used, I can't say... My best advice: Google repeating groups.

The second employee table is an alias table (tblEmployees_1); does this count as a second table, and should it be removed?

I think so, yes... I am assuming the two tables will hold the exact same data... If so, it will be more difficult than otherwise to maintain them...

The employee table currently has very few fields. The main purpose of the employee table is to show which employee either opened or closed the incident, so i'm not too worried about populating more fields with employee information...but that could change in the future.

Currently we're not too concerned with reassigning an incident to different employees, so an extra table to facilitate the many to many relationship doesnt seem to be a requirement (at least not right now)

It's almost always easier to up set things like this earlier rather than later. You may want to cover yourself by alerting your employer that setting up your junction tables to handle this may save you or another person a fair amount of pain in the future. Generally, try to design for change: if your application is well-received, people will see other uses for it and ask you if you can incorporate more features...hard to anticipate all requests but it's best to make your application pliable -- easier said than done, of course.

Regards,
Tim

 

[AccessNooBie]

Much thanks for the input.

 

[rhett7660]

It's almost always easier to up set things like this earlier rather than later. You may want to cover yourself by alerting your employer that setting up your junction tables to handle this may save you or another person a fair amount of pain in the future. Generally, try to design for change: if your application is well-received, people will see other uses for it and ask you if you can incorporate more features...hard to anticipate all requests but it's best to make your application pliable -- easier said than done, of course.

This is soooo true. I have only started working in access and man.. they see what you asked them to do then, they ask well can it do xyz?.....etc... What I try to do now is plan for what they might want it to do....

 

[CraigDolphin]

Quote:

The second employee table is an alias table (tblEmployees_1); does this count as a second table, and should it be removed?

I think so, yes... I am assuming the two tables will hold the exact same data... If so, it will be more difficult than otherwise to maintain them...

Not sure I agree here. From what I see, the 'two' tables are the exact same table being shown in the relationship view twice to ensure that data in the two fields referencing employees obey referential integrity. There is no maintainence issue here because there is only one table: even if it appears more than once in your relationship view.

If these are actually two tables that contain 'identical' data, each being shown only once in the relationship view, then there would be a table maintainance issue because you would have to make the data in one table always match the data in another 'identical' table.