Re: Tables cities and table user

On 2/9/19 10:09 AM, ^Bart wrote:
> Hi Everybody!
>
> I need to connect a table named users to the city's tables but I don't
> know if this below could be the right choice:
>
> cities_it
> -------------
> id_city
> name
>
> cities_uk
> -------------
> id_city
> name

Why two tables?

cities

This eliminates all the "national" tables and your original cities table and you can then directly connect your user to the city.

select * from users u
inner join cities c on c.city_id = u.city_id

> cities
> id_city
> FK_cities_it_id_city
> FK_cities_uk_id_city
>
> users
> ------------
> id_user
> name
> FK_cities_id_city

With your current design you would need

select * from users u
inner join cities c on c.id_city = u.FK_cities_id_city left join cities_uk cu on cu.id_city = c.FK_cities_uk_id_city left join cities_it ci on ci.id_city = c.FK_cities_it_id_city

and for each more country you add you need one more column in cities table and one more left join in your select and the query would get slower and slower with the number of tables. Also your column names makes it difficult to which id to use, it's easier if you have a name that will be the same in all table which is actually the same value.

FK should be a constraint in the database, not something you add to a column name, for mysql you need to choose a database engine which supports FK.

-- 

  //Aho