ok need a bit of help with creating a database for the structre of my company, the attached image is an example of the hierachy in my company (ignore the names)
I’m ok with the staff table but my main problem is with the department table. This is how I had it at first
DeptID (primary key)
DeptName
DeptHead (staff members id - foreign key)
Level
Parent
In the image you should see the level number beneath the dept name, fairly obvious what I thought here. And again Parent refers to the parent field directly above, for instance in this case the parent of the Hats Department would be Candles, parent of Candles would be Bakery, parent of Bakery would be board…
now onto the problem…
With this example if I wanted to list everyone in the hats department I could use an SQL statement roughly like this
SELECT * FROM Staff,Department WHERE (Department.Dept=hats or Department.Parent=hats) AND (Staff.DeptID=Department.DeptID)
now what this would return would be everyone in the hats and fishmonger department…however I also need to list everyone in Candles, Bakery and Board…don’t think this is possible with my structure
kinda stuck, anyone faced this problem before :huh:
