CODEWITHSUNDEEP
mysqldatabase-design
dman
dman

Reputation: 11064

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

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

Upvotes: 1

Views: 89

Answers (2)

Billy Chan
Billy Chan

Reputation: 24815

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)

Upvotes: 2

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79919

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

Upvotes: 1

Related Questions