Database Design Question

[Michael_Burgess]

Hi there,

I'm fairly new to database design, having only really worked with
existing tables etc in the past.

Simple question this really...........

In a users table, is it wise to have a ManagerID column that is
effectively pointing to another user in the same table, with the
theory being that if that person is top dog, they will just have a
null entry in ManagerID.

I will check for circular references before entering the data.

Is there a better way of storing the data, or is this the only way?

Thanks,
Michael.

[M A Srinivas]

On Jun 8, 4:12 pm, Michael_Burgess <m...@marsh-hall-studios.co.uk>
wrote:

This is the way to store data , Otherwise you may require as many
tables as there are levels . You have to build a hierarchy column
for easy retrieval of child and parent

[Seribus Dragon]

I would Have A lookup Table because you may end up with a person having
more then one manager.
UserID|ManagerID

[Michael_Burgess]

> > Michael.- Hide quoted text -
>
> - Show quoted text -

Would you be able to give me a very trivial example with some mini-
tables at all please? Sorry to put you out.

Thanks,
Michael.

[Jason Lepack]

-- try this

create table employees
(
employee_id tinyint identity(1,1) not null primary key,
employee_name [varchar](50) not null
)
go

create table management_hierarchy
(
employee_id tinyint not null,
manager_id tinyint null
)
go

-- foreign key - employee
alter table
management_hierarchy with check
add constraint fk_mh_employee foreign key(employee_id)
references employees(employee_id)
go
alter table
management_hierarchy
check constraint fk_mh_employee
go

-- foreign key - manager
alter table
management_hierarchy with check
add constraint fk_mh_manager foreign key(manager_id)
references employees(employee_id)
go
alter table
management_hierarchy
check constraint fk_mh_manager
go

-- insert sample data
insert into employees(employee_name) values ('Jason Lepack')
insert into employees(employee_name) values ('Bobsyer Uncle')
insert into employees(employee_name) values ('Sweet Lee')
go
insert into management_hierarchy(employee_id, manager_id)
values(1, null)
insert into management_hierarchy(employee_id, manager_id)
values(2,1)
insert into management_hierarchy(employee_id, manager_id)
values(3, 1)
go

-- output the data
select * from employees
go

select * from management_hierarchy

select
e.employee_name subordinate,
m.employee_name manager
from
management_hierarchy mh
inner join employees e
on e.employee_id = mh.employee_id
left join employees m
on m.employee_id = mh.manager_id
go

-- drop the tables
drop table management_hierarchy
drop table employees

On Jun 8, 10:57 am, Michael_Burgess <m...@marsh-hall-studios.co.uk>
wrote:

[--CELKO--]

>> Is there a better way of storing the data, or is this the only way? <<

Get a copy of TREES & HIERARCHIES IN SQL for several different ways to
do an organizational chart. What you have is teh adjacency list
model; look up the nested sets model.