Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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

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

share|improve this question
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? – Mike Sherrill 'Catcall' 13 hours ago
one to many I guess..Company can have many employees, but employees will only have one company. – kdub 12 hours ago
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? – Mike Sherrill 'Catcall' 10 hours ago

1 Answer

up vote 0 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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