I have a Employee Table that stores unique Employee numbers in the emp_no column. The thing is if another user or company stores its Employees in the same table and have an employee with the same number e.g. 1001 that has already been used they cannot store there employee.
WHAT I want to know is should I create a new employee table for each new user or is there a different solution?
Advertisement
Answer
No, don’t create multiple employees table
Your case is very standard in the SQL world, it’s what we call a 1 to N relationship (or one to many). A company can have many employees but an employee is linked to one company.
You need to create a company table with a unique company id and a company name.
Then, in your employee table, you add a new column with a foreign key constraint to the company id column.
Finally, in your employee table, set the unique constraint on the couple (emp_no, company_id) so that the unicity constraint represents your real world constraint.