Company vs Employee ID dilema

Question

I have two tables in my SQL database:

Company: - ID (autoincrement) - name - address - ...

Employees: - ID (autoincrement) - Company_id - internal_id - name - lastname

The problem is that I would like to have a employee id (internal_id) that is relative to the company they belong to. I got this dilema since I'm really been searching what would be the cleanest way to implement it.

One option would be to just make a kind of SELECT MAX(internal_id) FROM employees WHERE company_id = X, but the problem would be that if I happen to delete the last employee the next one would be created with the ID of the next.

Any ideas or suggestions?

PD: The reason of why I want to do this is that i dont want a user from company X create an employee that is for example ID=2000, while the last employee created in his company was, say, 1532. this would normally happen in a system in wich Company Y and Z also create employees on the same system. I want this ID not to use as a foreign_key, but to have it for internal (even documents or reports) use.

PD2: In this case the employees will never have to change companies

UPDATE

Thanks to all, there is really good advice here... It was hard to choose which was the best answer to me.. I picked that one beacuse I think is the simplest.

Guillermo

Answer 1

You should never delete a row from a relational database. You should expire that employee (IE, Set Employee.ActiveFlag = 0)

So if you had an internal_id (int) and did a (SELECT MAX(internal_Id) +1 FROM Employees WHERE Company_Id = [parameterCompanyID]) then that would work fine.

Answer 2

if you are going to use the employee id as a fk, would you really want to delete it and all of their work in the DB? Make a status field in employee and set it "A"ctive or "I"nactive. The EmployeeID is best if it stays unique across all companies so just let it be autoincrement. Never let the users see the IDs, let them see the names only.

also don't name the columns ID, CompanyID or EmployeeID, will be more readable and easier to search on.

Answer 3

Why not use GUIDs for your internal_ids? That way, you can always just do you inserts without fear of ID collisions.

You will not be able to avoid the delete anomaly given your current structure, but you really shouldn't do it the way you are proposing anyway. In order to reliably get the "next number," you will have to lock the table and that will cause all kinds of bad. Perhaps you can implement some kind of soft delete for employees as they tend to go and come sometimes anyway. :)

Answer 4

There are lots of questions here about creating "Business" IDs or numbers that are unrelated to the primary keys.

In your case I would create a column on the Company table "NextEmployeeID" Then when creating a new employee simply retrieve the value and increment it.

Now I leave it up to you to figure out what happens if the employee changes companies. :-)

Answer 5

No, don't do that! This will create many many problems in your database (you have to worry about concurrency issues among other things) to solve something that is NOT a propblem and is, in fact, correctly designed. The id should be meaningless and gaps are unimportant. You would want a unique index on the employeeid/companyid combination to ensure no employee is assigned to multiple companies.

Your employee id should be something that never needs to be changed. If you make some sort of silly company based ID and company A buys out company B and becomes company C, you end up having to change all the ids and all the related tables. In your current design you only need to update the company code but not the related tables.

Answer 6

Create a separate table:

CREATE TABLE t_identity (company INT NOT NULL PRIMARY KEY, id INT NOT NULL)

and issue:

INSERT
INTO    t_identity (@company, 1)
ON DUPLICATE KEY UPDATE
SET     id = id + 1

before inserting a new employee.

Answer 7

I would create a separate table, as Quassnoi suggested, with the company and the current max sequence number. But I would wrap it in a stored proc or UDF (does MySQL do UDFs?) that accepts the company as an input, increments the current value and returns that incremented value.