[Resolved] FK's in mult. tables

[travelbuff]

Hi all:

Let's say I have a table structure like this:

CITIES
city_id
city_name

STATES
state_id
state_name

USERS
user_id
user_name
cities_city_id (FK)
states_state_id (FK)

Then a DIRECTORY table that has users_user_id, along with other fields. Do I also need to include the FK's from USERS in the DIRECTORY table since I have a USERS FK?

The reason I ask is the I was using FabForces DB designer and it does that by default - but I don't see a purpose for it, and a problem would be that all of the foreign keys would need to be updated if a change was made (since mysql doesn't manage those relationships).

Hope this makes sense

Thanks

[kpzani]

really your structure should be

USERS
user_id
user_name
cities_city_id (FK)

CITIES
city_id
city_name
state_id (FK)

STATES
state_id
state_name

then just the user_id in the dir table. otherwise you have a multiple foreign key condition which could mess up the db structure.

[travelbuff]

Thanks kpzani, that's what I thought. It really threw me for a loop when DBdesigner did it that way.