Database Design Question

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.

Re: Database Design Question

On Jun 8, 4:12 pm, 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.

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

Re: Database Design Question

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.
>

Re: Database Design Question

On Jun 8, 2:56 pm, Seribus Dragon 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.

Re: Database Design Question

-- 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
wrote:
> On Jun 8, 2:56 pm, Seribus Dragon 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 -

Re: Database Design Question

>> 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.