CODEWITHSUNDEEP
phpmysqldatabasedatabase-design
Guillermo
Guillermo

Reputation: 947

Company vs Employee ID dilemma

I have two tables in my SQL database:

Company:

Employees:

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

Upvotes: 2

Views: 1613

Answers (8)

Khurram Qureshi
Khurram Qureshi

Reputation: 1

I would rather create 3 columns for 3 variables and allow Admin to modify the values in database! 1. COMPANY SHORT CODE (companyCode i.e. COMP) 2. COMPANY DEPARTMENT SHORT CODE (depCode i.e. DEP) 3. COMPANY START COUNT VALUE (compVal i.e. 00000) Now When adding new Employee I would Join all variables and Increment and Update compVal++ each time! (i.e. COMP-DEP-0001) or We can also add date() with Employee ID

Upvotes: 0

John M Gant
John M Gant

Reputation: 19308

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.

Upvotes: 0

KM.
KM.

Reputation: 103607

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.

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425491

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.

Upvotes: 4

HLGEM
HLGEM

Reputation: 96590

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.

Upvotes: 12

DJ.
DJ.

Reputation: 16247

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. :-)

Upvotes: 3

JP Alioto
JP Alioto

Reputation: 45127

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. :)

Upvotes: 0

Nathan Koop
Nathan Koop

Reputation: 25197

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.

Upvotes: 0

Related Questions