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
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