SQL Server Performance Forum – Threads Archive

To put two fields based on another table

Hello! I have a doubt with two tables. Table1 calls ‘Trip’ and Table2 calls ‘Cities’: Table Trip:
Id_Trip
From_City
To_City
Id_City
Table Cities:
Id_City
Name_City I want that a user can choose a ‘city from’ and a ‘city to’ from ‘Trip’ table based on the ‘Cities’ table. How can I do this? Is correct the Id_City foreign key? I don’t want to fill in the ‘Id_City’ field from the ‘Trip’ table, only the ‘From_city’ and ‘To_city’ fields based on ‘Cities’ table. Thanks,
Cesar

What is the purpose of the Trip.id_city field ?

Ok, drop(erase) the Id_City field in the ‘Trip’ table. How can I join the two tables so that the user only can choose the cities that contains in the ‘Cities’ table? Obiously can choose two fields: â€˜From_City#%92 and â€˜To_City#%92.

I don’t see the reason for a join here. In your GUI (VB app, web app etc.) have a select list/combo box that shows the cities. When you select a "from" city enter it’s ID in the From_City column and when you select a "to" city enter it’s ID in the To_City column. /Argyle

Thank you. But I thought that one of the main rules of a database design was that when you base a field with another field from another table you needed to join those tables to demand referential integrity. Anyway thank you,
Cesar

You will join them when you want to get the Name_City of From_City or To_City. And you will show the list of cities getting all values from table Cities as Argyle suggested. You can add a FK on Cities.ID_City – Trip.From_City, Cities.ID_City – Trip.To_City to enssure values will be only those present in the Cities table. Bambola.

Oh yes! [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />], it’s just that I really wants; two FK like this.<br /><br />Thank you Bambola!<br />Cesar

]]>