Database design question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
c0mrade
Forum Newbie
Posts: 19
Joined: Mon Oct 06, 2008 4:17 am

Database design question

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Database design question

Post by VladSun »

There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Database design question

Post 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".
There are 10 types of people in this world, those who understand binary and those who don't
c0mrade
Forum Newbie
Posts: 19
Joined: Mon Oct 06, 2008 4:17 am

Re: Database design question

Post by c0mrade »

Thank you vladsun, that is the exact thing I needed .. thank you
Post Reply