Page 1 of 1

Database design question

Posted: Sun Sep 20, 2009 1:54 pm
by c0mrade
Hiya all, I'm currently developing a website for a hotel. And one of the things I'm about to implement is worker->superior relationship. What is the best way to do so in MySQL? Here is what I mean ex: a chef's superior is a head chef, heads chef superior is shift manager, shift managers superior is general manager. I could make in the employee table, field superior with ID of superior employee but then I'm only able to get one superior/upper role, more importantly I wouldn't be able to retrieve list of all employees that manager manages at particular hotel. Hopefully this is somewhat clear what I'd need advice on .. thank you for your effort

Sorry I forgot to mention that one employee can have at most 1 superior, so its more important to have hierarchy from top until bottom like general manager can view data of all employees. Unlike shift manager who is able to view data of all employees but not able to view data of other shift managers.

Tnx

Re: Database design question

Posted: Sun Sep 20, 2009 3:04 pm
by VladSun

Re: Database design question

Posted: Sun Sep 20, 2009 3:07 pm
by VladSun
c0mrade wrote: Sorry I forgot to mention that one employee can have at most 1 superior, so its more important to have hierarchy from top until bottom like general manager can view data of all employees. Unlike shift manager who is able to view data of all employees but not able to view data of other shift managers.
You will nees one or more "owner" ID foreign keys in every record of "data".

Re: Database design question

Posted: Mon Sep 21, 2009 8:24 am
by c0mrade
Thank you vladsun, that is the exact thing I needed .. thank you