Unanswered: How do I create a one-to-one relationship, and do I need to?
In this database I'm creating I have a customer and an employee table. Instead of repeating information from both tables, ie an employee can be a customer and a customer can be an employee I wanted to have a separate employee table with just the hire date, employeeID and customer ID to link them together. The customerID is an autonumber field but instead of some random number, for employees it's significantly easier for employees to pick their own ID for use in logging in and using the POS, work order etc. As such I need the employeeId to be the primary key. If I link the customerID from one table to the employeeID in the employee table, then the numbers need to be the same, right? How can I create a one to one relationship and yet still be able to create the employeeid, and use it as foreign key in other tables as needed? I have attached a picture of the table as it's currently set up. If anything is unclear please let me know and I can elaborate more. Thanks!
It sounds like you have stumbled onto your own version of a modelling technique called sub-type super-type.
Have a google at these - see if it is a more concrete idea of what you want. In essence you would have a person table (the super type) and an employee and a customer table (the two sub type tables). All with one-to-one relationships.
Thanks, I think you're correct. The situation I want to resolve if it's possible is to allow the employee to choose their own ID to use for POS, Work Order, Purchase Orders etc. Is there a way to create a super/subtype where the EmployeeID can be different while still being the PK?
Exactly! I can't figure out how to make that work. Ideally I'd like to be able to either choose the ID when entering as a customer if wanted, or change it after a customer becomes an employee as it sometimes does. Of course sometimes the Employee has never shopped here, but then will start to shop here, so it's helpful to be able to have a great deal of arbitrary control over the value of the personID