Posted by Jason Lepack on 06/08/07 19:17

-- 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:
> On Jun 8, 2:56 pm, Seribus Dragon <Seribus.n...@seribus.com> wrote:
>
>
>
>
>
> > I would Have A lookup Table because you may end up with a person having
> > more then one manager.
> > UserID|ManagerID
>
> > Michael_Burgess wrote:
> > > 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.- 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.- Hide quoted text -
>
> - Show quoted text -

[Back to original message]