1

I'm trying to set my logic for a small application. Does this seem ok for the database. I want that every department to have only one manager (function from employee can be simple/manager). if i did separate tables for managers and simple i probably had to do this for users too, right ?

EMPLOYEES #id_emp,function,name_emp,adress_emp,salary_emp,@id_dep
USERS,#username,@id_emp,password
DEPARTAMENTS,#id_dep,@id_manager,dep_name

# - primary key
@ - foreign key
Alex K.
  • 171,639
  • 30
  • 264
  • 288
dskfdskjgds
  • 341
  • 3
  • 14
  • I would think that if the only difference between a manager and a non-manager is the value of the one column, then leave them in the same table and make sure your business logic only allows one manager per department – Taegost Jul 28 '15 at 13:34
  • With this design, you can have a employee without a corresponding user record. It may be better the invert the FK between USERS and EMPLOYEES such that an employee is guaranteed to have a user record (FK from EMPLOYEES to USERS) – HashPsi Jul 28 '15 at 13:35
  • Create complex primary key in DEPARTMENTS with id_dep and id_manager (http://stackoverflow.com/questions/5835978/how-to-properly-create-composite-primary-keys-mysql) – starko Jul 28 '15 at 13:36
  • i use this logic for a small practice website. i think it's ok that an employee exists but he doesn't have an username associated but it wouldn't be ok to have an user account without being an employee – dskfdskjgds Jul 28 '15 at 13:37
  • thank you. that should work – dskfdskjgds Jul 28 '15 at 13:37
  • @dskfdskjgds Your schema looks ok for the given requirements. Perhaps change the `function` field name to a non-reserved word. – reaanb Jul 28 '15 at 13:39
  • I would remove the function column. when selecting an employee, you can check if it's id exists in id_manager in departments table to find out if the employee is a manager or a simple employee. – Zohar Peled Jul 28 '15 at 13:40
  • @starko I disagree, I believe departments are uniquely identified by surrogate key id_dep. A unique constrain on id_manager may be in order, and that would make it a candidate key as well, but a poor choice for a primary key. – reaanb Jul 28 '15 at 13:41
  • yes. but i think that means a little more work for the server to do -> a slower response – dskfdskjgds Jul 28 '15 at 13:42

0 Answers0