Table Design

[lloyd33]

I have designed a contact database and would like some feed back before I dive in any further.

The Plan:
I have two main tables, Personnel and Contacts which is a subset of the the Personnel table. The personnel table contains the fields common to both Employees and their contacts. I have also placed the PersonnelID into the Contacts table twice. 1. to link contacts, and two identify which employee created the contact. (Employeeid in the contact table.)

I have a third table called type which I used to distinguish between employees and contacts.

I recently learnt that if two entities share many common fields then should not be in separate tables. Instead separate only the fields not common into separate tables.

I have tested this design and it is doing what I would like. Question have I designed this table correctly.

 

[Mile-O]

Maybe a table for City as you may - I don't know your geography - find that repeating itself over time.

 

[lloyd33]

thanks for that suggestion good idea. Can anyone else see a problem with my design?

 

[lloyd33]

work number is the main switch board number and direct line is the direct number to your desk phone. You mentioned that I need another relationship between employeeid and the personnel table to enforce referential integrity. Why?

 

[lloyd33]

thanks to those that helped!. If anyone else has any more suggestions let me know. Thanks

 

[a.sinatra]

What is "Area"?

Is that the state, cause i noticed there was no "State"

And belive it or not people still live in Appartments, so you might want to do yourself a favor and add "ApptNo" or something along those lines so you dont run into this problem down the line about someone living in a Appt. and no where to store their Appt. Number.

Post your relationships again, lets see what you got going now
________
YAMAHA TRX850 SPECIFICATIONS

 

[lloyd33]

there is no state as db is for the UK.

I have added the personnel tbl twice as suggested by Pat. If i do not add this table twice could I use a query instead to look up the values of employees only, i.e. the type field = "Employee".

If not why is adding the personnel tbl twice stronger than using a query. My thinking is I do not need to add the table twice and a query will achieve the desired results. Because without a query I cannot display just employee info in the combo for CreatedByEmployeeID.

 

[lloyd33]

Thanks Pat.

 

[lloyd33]

Possible Table design

Hi All I have updated my relationships based on suggestions from the forum. About to implement this design if anyone else has any suggestions please let me know before I implement it.

For all those that did help me by answering my various questions, espically Pat THANKS.