Need Help with Structure (1 Viewer)

Let me first say I am a self-taught novice when it comes to databases so please take that into consideration when you read and reply. I am creating a MS Access 2013 database to track calls, questions, assignments and other related work for my human resources department. We will lump all types of calls together and call them "cases". The main purpose of the database is to have a place where everyone who works on a case can enter their notes and we can have a running record as the case is worked. Eventually, we will be able to get metrics on how many cases, how much time spent, who the cases were about, who the customers where, who worked what cases, etc.

Cases come into the HR department for all company employees so I know I need a table for employee data. I also need a table for the customers who make the requests.

Here is where I am stumped -- the majority of the customers are the same people as the employees and a customer can also be the employee themselves if they call about their own information. If the customers were always external people I would just make a second table called customers and move on. To complicate this a little more, I also have external customers besides the employees.

To be efficient I don't think I should duplicate all of the employee data in two tables (employees and customers) but since there are also external customers -- I don't know the best way to structure the table(s)? Do I make two or three tables and join them somehow using primary and foreign keys?

My thought would be to make a table for employees and include a field for employeeIDNumber and another field for customerIDNumber. Then make a table for external customers and also give them a field for customerIDNumber. But then I'm not sure how to make sure I don't end up with duplicate customerIDNumbers?:banghead:

Thanks for the help -- DocEE13

I would keep both employees and customers in same table and use a checkbox to mark whether the person is employee or customer.

JHB said:

I would keep both employees and customers in same table and use a checkbox to mark whether the person is employee or customer.

Click to expand...

I would not do that because what will you do when someone is a customer and an employee.

What I would do is to have one table called People(or similar) and then link to this from the Case table as applicable.

Rabbie said:

I would not do that because what will you do when someone is a customer and an employee.

Click to expand...

What should the problem be with that?

Rabbie said:

What I would do is to have one table called People(or similar) and then link to this from the Case table as applicable.

Click to expand...

But then you still don't know if it is an employee or a external customer who made the requests.
I would make the same setup with my table.

JHB said:

What should the problem be with that?

But then you still don't know if it is an employee or a external customer who made the requests.
I would make the same setup with my table.

Click to expand...

I think that both of us are suffering from the fact that we do not have full info from the OP as to the significance of the difference between customer and employee especially when the employee is acting as a customer.

I suspect I may have misunderstood your suggestion and we are basically saying the same thing.

I would do:
LEFT JOIN from People to EmploymentDetails - if there's an employment detail that person is an employee. All cases would be opened based on People.

The details held in People would depend on commonalities between customers and employees. And EmploymentDetails can/should be broken further.

I think this on the same lines as Rabbie.

Hi ,

Probably best not to JOIN so early on in your application, or for such a simple task. KISS! (as in keep it simple lol) Simply add one or more fields depending upon the logic you wish to enact.

It is probably best to sit down with your boss and review things like this regularly, get them to sign-off on all decisions as it will win you points and confirm you don't go off the reservation too wildly from their needs.

Also try as much as possible to gauge what the people in the HR office want as they will be the users, but ultimately you will probably end up maintaining it and troubleshooting their issues as well as justifying decisions to your boss.

Best of luck, and if it gets too hot, throw your hands up and tell your boss to get an expert in! It's not fair on either side for your boss to be disappointed when it seems as though you may have told them already you are a learner with these things; or for them to lump pressure on if you start and have difficulties!

I'm sure the above wont be the case, I am just saying start easy, gradually and at your own pace, I'm not sure I would have been too comfortable if I had been learning at work :S

Enjoy the weekend!