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

Quote:

Originally Posted by

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|||I would Have A lookup Table because you may end up with a person having
more then one manager.
UserID|ManagerID
Michael_Burgess wrote:

Quote:

Originally Posted by

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

|||On Jun 8, 2:56 pm, Seribus Dragon <Seribus.n...@.seribus.comwrote:

Quote:

Originally Posted by

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

Quote:

Originally Posted by

Hi there,

>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

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.

>

Quote:

Originally Posted by

I will check for circular references before entering the data.

>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

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

Quote:

Originally Posted by

On Jun 8, 2:56 pm, Seribus Dragon <Seribus.n...@.seribus.comwrote:
>
>
>
>
>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

Michael_Burgess wrote:

Quote:

Originally Posted by

Hi there,

>

Quote:

Originally Posted by

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

Quote:

Originally Posted by

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.

>

Quote:

Originally Posted by

Quote:

Originally Posted by

I will check for circular references before entering the data.

>

Quote:

Originally Posted by

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

Quote:

Originally Posted by

Thanks,
Michael.- Hide quoted text -

>

Quote:

Originally Posted by

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

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