Foreign Key Design...

[Steven]

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

[pietlinden]

Looks like someone going normalization crazy. I mean, normalization is
good, but no need to go overboard. If a user need not be an employee,
just combine the Employee and User tables and put a boolean IsEmployee
field or whatever. The postal code gig. DO you really need this level
of normalization? How big is your database going to be?

[Nick 'The Database Guy']

Hi Steve,

No matter what others might say if you are going to design a database,
big or small, you may as well do it right.

There is no advantage in doing things in half measures, however it does
have to be said that you are taking things to extremes slightly. The
amount of time and effort it would take to efficiently program a
database to recognise the country from postcode, or city would be more
trouble than it is worth. I would suggest default values in the data
entry forms that you are going to use, and the storage of the address
in one table.

Good luck

Nick

Steven wrote:

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

[Tim Marshall]

Steven wrote:

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.

Just off the top of my head...

Do the older programs still add records? If not, then perhaps you could
link to the older tables into a new mdb. Write queries that present
the old data in the format your new system will use. Group by (Totals)
queries might be a help, and/or you could use a large number of sub queries.

Where your new application must display old data with the new, use union
statements to combine new tables with the above queries. In procedures
where you're adding/editing new data, you probably only need the new tables.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me