MySQL simple question

Anon

Hi. My question should be done to a mysql forum but their support isn't that simple, so i post it here for a quick answer.

I'm a beginner in MySQL and i want to learn the following :

Suppose we have 2 tables. A users_tbl and a cities_tbl. I guess we should have cities in a different table for better normalization. How do we tell the db that the "user's city info" should be linked and taken from the cities_tbl.

This is an example. My question in general is how do we link tables in MySQL like Access let's say. I couldn't figure this out from the mysql documentation.

Thanks.

John

metuin

Give each tbl an ID from the other so you'll get :

SELECT * FROM cities_tbl WHERE users_ID='$users_ID';

Anon

Thanks Tuin but my question is not about PHP and MySQL. It's only about mysql and how to create a db where the tables are linked. For example the cities_tbl has 2 fields

City_ID City_name

1 London
2 Paris

Then in the users_tbl i want the user_city to be 1 or 2 and the db to know that these numbers are related to the cities_tbl.

It's pure mysql question and sorry for posting it in this forum.

John

Anon

The Users table should hold a 'foreign_key' to the User_City table, a sort of reference to the User_City table from the Users table.

This means that the Users table will look something like:

User_ID | User_name | City_ID

111 Jacques 2

and the User_City table would look like this:

City_ID City_name

1 London
2 Paris

Try this...

DAV

Anon

Yes. It's the foreign key that i'm looking how to make it. In the documentation it has very little help and it shows that you have to make another table that has the 2 primaries from the other tables. Is this the only way?
Then it's useless because i will have to update one more table.

Forgive my ignorance 🙂

Thanks

John

matto

a table which holds 2 foreign keys?
depends on the kind of relation.

if you have an 1:n relation (e.g. 1 city has got n persons), do it as described in the postings above (one foreign key in the person table).

if you've got an n:m relation (1 person has got n cities and 1 city has got n persons), you need a third, relation table, e.g.:

person2city

id_person id_city
1 1
1 2
2 1
(...)

look for some database modelling tutorial or article; start in phpbuilder's articles section.