1

Thanks for reading my post...I'm new at setting up databases and am working on a simple practice app.

I have a table called PhoneNumbers, which hold a list of phone numbers (duh). I have two other tables that relate to PhoneNumbers, Employee and Company.

Each Employee can have multiple phone numbers, each Company can have multiple phone numbers.

Since my app needs to be able to separate out the difference between Company phone numbers and employee phone numbers, and be able to list each number per employee and per company, I was wondering the best way to set up the PhoneNumbers table, keeping in mind that perhaps there will be more than two Number Types in the future.


One ideas I had was:

Create two fields in the Phone number table called NumberTypeID and OwnerID and create another table called NumberType. NumberTypeID would hold the ID numbers of the Number Types created in the NumberType table. OwnerID would hold ID of the Company or Employee it belongs to.

PhoneNumbers 
 - ID
 - PhoneNumber
 - NumberTypeID
 - OwnerID

NumberType
 - ID
 - Name

I'm thinking the NumberTypeID column will help querying all numbers for all companies/employee easier. I also am thinking this makes it more flexible down the road for new number types, but perhaps is overly complex? Maybe I don't need the NumberTypeID column at all and querying all numbers for all companies/employees is best with a more complex statement saved as a view? Or perhaps I'm missing something else all together?


Again very new at this, so any help would be greatly appreciated.

Thanks

4
  • I can imagine several different 5NF schemas for this. It depends mostly on how you want to model employer/employee relationships. How do you want to do that? Apr 21, 2013 at 0:19
  • one to many I guess..Company can have many employees, but employees will only have one company.
    – kdub
    Apr 21, 2013 at 0:54
  • Over the course of their careers, employees will have many companies. But at a single company, they also have something like a department (maybe more than one), a hire date, maybe a termination date, etc. What additional information do you want to store besides "Fred works for Slate & Co" and "George works for Spacely Sprockets"? Anything? Apr 21, 2013 at 3:10
  • oh I see what you asking...No nothing like that, this is just a very simple practice app I'm doing. When I came to the phone numbers table, I just wasn't sure which way to go
    – kdub
    Apr 21, 2013 at 14:29

3 Answers 3

1

One question I have is how does the Company relate to a phone number? I mean, is there just a list of internal numbers, and will all of these phone numbers be associated to an employee. If they are you can simply just make your phone number table and have it relate to an employee. For example.

create table company
(
company_id int PRIMARY KEY IDENTITY, --(Primary Key)
company_name varchar(100)
)

create table employee
(
employee_id int PRIMARY KEY IDENTITY,  --(Primary Key)
employee_name varchar(100),
company_id int not null  --(Foreign Key)
)

create table phone
(
phone_id int PRIMARY KEY IDENTITY,
phone_number varchar(15), -- 555-555-5555
phone_type varchar(10),   -- Home | Cell | Work
employee_id int not null  --(Foreign Key)
)

employee_id being your foreign key to your employees table. If each employee only works for one company, then to get all of the phone numbers available for that company, you could do something like this

select * from phone as p join employee as e on e.employee_id = p.employee_id where e.company_id = 1

I hope this helps :)

1

This is an interesting one which is complicated by the relationship between employees and companies.

To have a number of different tables (employees, companies, etc.) all realted to phones in a one-to-many way you're looking at having join tables (as with many-to-many joins), because you can't store all the parent ids in the phone table. That would look something like this:

create table phone
(
    id int,
    phone_number varchar(15)
);

create table employee
(
    id int,
    name varchar(100),
    company_id int
);

create table company (
    id int,
    name varchar(100)
);

create table employee_to_phone
(
    phone_id int,
    employee_id int,
    foreign key (phone_id) references phone(id),
    foreign key (employee_id) references employee(id)
);

create table company_to_phone
(
    phone_id int,
    company_id int,
    foreign key (phone_id) references phone(id),
    foreign key (company_id) references company(id)
);

Meaning if you want to get a list of employee names and their phone numbers, you'd run:

select e.name, p.phone
from employee as e
inner join employee_to_phone as j
on j.employee_id = e.id
inner join phone as p
on j.phone_id = p.id;

The complication is that employees are related to companies. So, you might want to get a list of phone numbers which either a) belong directly to a company or b) belong to an employee of a company. In that case, you'd need to do something like:

-- to get phone numbers allocated to employees
select c.name, p.phone_number
from emplyee as e
inner join employee_to_phone as j
on j.employee_id = e.id
inner join phone as p
on j.phone_id = p.id
inner join company as c
on c.id = e.company_id
-- to get phone numbers allocated directly to companies
union
select c.name, p.phone
from company as c
inner join company_to_phone as j
on j.company_id = c.id
inner join phone as p
on j.phone_id = p.id;

This feels a little messy but the other option is to duplicate the data in employee_to_phone in company_to_phone which is a terrible idea, as you'll have to keep both tables up to date when an employee moves etc.

3
  • 1
    Why couldn't you store all parent IDs in phone number table? My idea for NumberType and Parent ID was that if I wanted to query all phone numbers for all employees, it would be something like: SELECT Number FROM PhoneNumber WHERE NumberType = 1 (where one is the id for employees in the NumberType table). If I wanted to query one employee's phone number it would be something like SELECT Number FROM PhoneNumber JOIN Employee ON PhoneNumber.ParentID = Employee.ID
    – kdub
    Apr 26, 2013 at 1:34
  • It is possible to implement something like that, but you are then not able to use a foreign key from parent id to enforce consistency (as the key would have to point to multiple tables). So, an employee could be deleted leaving behind an invalid reference in the phone number table. I'll amend my answer with foreign keys (I should have done this before really!). I guess you could use triggers to enforce consistency with your model, and since you said you're new to setting up dbs now might be a good time to look into that; just get thinking about consistency enforced at the db layer!
    – Matt
    Apr 26, 2013 at 6:50
  • (I still prefer the use of foreign keys for various reasons, including the increasing complexity of the triggers needed for phones which has to decide which table to check on update/insert and the likelihood that someone else adding another parent table may not be aware of the triggers and not update them, allowing inconsistent data for that parent type.)
    – Matt
    Apr 26, 2013 at 7:27
0

one simple way of doing it is two many to many tables

employee_phones {emp_id , phone_id}
company_phones  {comp_id , phone_id}
phones          {phone_id , phone_number , ... }

the advantage here is that you keep all your phones in one table. the disadvantages here are that 1. if you want to retrieve a phone number there is an additional join to the relationship table 2. you would probably want to retrieve only an employees or only a company phone - so the join would process unnecessary data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.