MySql Database Design- most efficient for columns that might be null?

Question

In this database sometimes the employees are in just one jurisdiction, sometimes more than one. For instance, Bob's jurisidictions would be North America, South America, and Africa. While Janes would be just North America. However, each employee will always have a department.

Since some empoyees will only have one jurisidiction assigned to them while others may have more, is this the best way to implement jurisdictions? Or is there a more efficient way?

I will be using join statements and selecting by department for my queries.

employees
-----------------
userID (primary key)
deptID (foreign key and NOT NULL references departments)
firstName
lastName
jurisdiction1 (foreign key references jurisdictions)
jurisdiction2 (foreign key references jurisdictions)
jurisdiction3 (foreign key references jurisdictions)
jurisdiction4 (foreign key references jurisdictions)
jurisdiction5 (foreign key references jurisdictions)
jurisdiction6 (foreign key references jurisdictions)
jurisdiction7 (foreign key references jurisdictions)

jurisdictions
-------------------
jurID primary key
jurisdictionName


departments
--------------------
deptID primary key
departmentName

because using a employee-jurisdiction mapping table would be...? — Najzero
– Najzero, Commented Dec 13, 2012 at 6:46

Billy Chan · Accepted Answer · 2012-12-13 09:21:00Z

2

The jurisdiction table design is not correct in your scheme. You need a intermediate table to store the relationship between user and region. Like this.

employee table
--------------
user_id
first_name
depart_id
j_id

jurisdiction table
-----------------
j_id
region

employee_jurisdiction
------------------
id
user_id 
j_id
(Set INDEX as no duplicate for user_id+j_id)

edited Dec 13, 2012 at 9:21

answered Dec 13, 2012 at 6:51

Billy Chan

24.8k4 gold badges56 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dman Over a year ago

Why do you prefer not to use a foreign key? What would you use instead?

Billy Chan Over a year ago

@dhee, please forget my last claim in answer which makes things complicated. Foreign key is necessary, it's okay to set it directly in database scheme.

Mahmoud Gamal · Accepted Answer · 2012-12-13 06:48:20Z

1

In this case, it is better to have a new table jurisdictions, and EmployeesJurisdictions:

Jurisdictions:

Id,
JurisdictionName.

UsersJurisdictions:

EmployeeId Foreign key references Employees(EmployeeId).
JurisdictionId a foreign key references jurisdictions(jurisdictionId).

answered Dec 13, 2012 at 6:48

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

5 Comments

dman Over a year ago

Should the UsersJurisdictions table have a primary key? Also, the UsersJurisdictions table foreign key "employeeID" is not updating and showing the user I inserted in employees table. I made sure I am using innodb engine.

dman Over a year ago

Actually, is there a way to make the UserJurisdictions table foreign key EmployeeID populate automatically when I insert a record into empolyees table?

Mahmoud Gamal Over a year ago

@dhee - For the table UsersJurisdictions you have to choices. Either create a composite key (EmplooyeeId, JurisdictionId), or create a new key, a surrogate key, a primary key Id. If you want to populate the EmployeeId automatically when you insert a record in the employees table, one way is to use Triggers create an after insert trigger on the empoloyees table, this triggers will fire automatically when any record is inserted in the employees table, in this trigger make you insertion into the other table.

dman Over a year ago

I've been doing alot of research on primary composite keys. If I made a primary key(EmplooyeeId, JurisdictionId) in table UsersJurisdictions, I would still use the same search such "SELECT * FROM employees inner join jurisdictions ON employees.userID = jurisdictions.employeeID"? What would be the benefit of the composite primary key rather than just 2 foreign keys without the composite primary key?

Mahmoud Gamal Over a year ago

@dhee - The composite key will ensure that the combination of the EmployeeID and JurisdictionId is unique. So each JurisdictionId is unique for each EmployeeId, and there is no difference when joining the two tables, it will be the same.

Collectives™ on Stack Overflow

MySql Database Design- most efficient for columns that might be null?

2 Answers 2

2 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related