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