Foreign Key Design...

Hello All,

I am relatively new to database design and wanted to ask a few
questions to see if I am on the right track regarding design and
normalization.

I am creating a user entity which is made up of 4 tables:

User(UserID(PK), EmpID, CountryID, PostCodeID)
Employee(EmployeeID(PK), EmployeeType)
Country(CountryID(PK), CountryName)
PostCode(PostCode(PK), CountryID(PK), City)

These are a few things I am trying to clarify
1) The Country table has a relationship with the User table via a
foreign key FKUser_CountryID, and the PostCode table has a relationship
with the User table via FKUser_PostCodeID_CountryID. I dont however
have any relationship between the Country and PostCode tables. Is this
okay or have I created these relationships incorrectly? My other
thinking was to create a location table:
Location(CountryID, PostCodeID, City)
Then the User table would have a relationship with location through the
Country and PostcodeID fields, and the Location table would have a
relationship with the Country table through the CountryID field?

2) There will be instances where the User is not an employee, so the
EmpID field will be null when no relationship exists between User and
Employee. Is it worth creating a boolean field in the User table called
IsEmployee to help checking in programs, or should I just rely on
checking whether the EmpID field is null in the User table.

3) Finally, much of the old data on the system has been created in
databases created many years ago. Unfortunately much of this old design
includes storage of redundant data, lack of constraints and no
normalization. However because many of the old programs would need to
be completely re-written if the schema of these tables were changed I
am really stuck using them. Does anyone have any advice on steps I
could take to improve the situtation.

I appreciate any help anyone can give me
Steve

.

Relevant Pages

  • Re: Table schema for user login system?
    ... good design dictates that I should. ... Definitely not normalization. ... this is not the correct newsgroup for discussion database design. ...
    (comp.lang.php)
  • Re: Database Design Problem
    ... The problem I have with putting the Product Catagory in ... > Design is a hard thing to do if you want a good design that is. ... There are lots of normalization rules ... Database Design is very difficult for me because I do ...
    (microsoft.public.sqlserver.programming)
  • Re: Conceptual design advice for relational database
    ... violates the first rule of normalization. ... Get a copy of "Database Design for Mere Mortals" by Michael Hernandez. ... Access Database Samples: www.rogersaccesslibrary.com ... Want answers to your Access questions in your Email? ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Database Design & Normalization Question
    ... Products, Other Titles, Title Types. ... However, oftentimes bad design will kill performance, no matter ... Normalization would dictate that these dates would depend on the KEY, ... > didn't have to change the database design to store new ...
    (microsoft.public.sqlserver.server)
  • Re: One example of a slow query.
    ... For the query that includes country, state, and city, the ... compound index than with multiple simple indexes. ... Why didn't I change the index design? ...
    (comp.databases)