MYSQL: Multiple values in column

[eMonk]

My database and columns:

province (province_id, ..)
city (city_id, province_id, ..)
model (model_id, age, height_in_cm, hair_color, measurements, eye_color, description, ..)
model_in_city (model_id, city_id)

Now I want to display all models in the chosen city by the user but each model can chose up to 4 city placements. How can this be done?

[k0nr4d]

Make three tables.

cities (varchar255 city_name, int11 auto incrementing ID)
models (with all your model info)
models_cities (model int11, city int11)

[k0nr4d]

Ahh you were listing tables not columns.

SELECT model.* FROM model WHERE model.model_id = model_in_city.model_id AND model_in_city.city_id = 12345

OR

SELECT model.* FROM model WHERE model.model_id = model_in_city.model_id AND model_in_city.city_id = city.city_id AND city.city_name = "Toronto"

[eMonk]

How would you have, for example, Monica displayed in Toronto, Chicago, New York and London under the models_cities table?

[woj]

Originally Posted by eMonk

How would you have, for example, Monica displayed in Toronto, Chicago, New York and London under the models_cities table?

add it 4 times into that table?

[k0nr4d]

Originally Posted by eMonk

How would you have, for example, Monica displayed in Toronto, Chicago, New York and London under the models_cities table?

You would insert multiple models_cities entries per model.

So lets say Toronto = 123
Chicago = 456
New York = 789

INSERT INTO models_cities (model, city) VALUES (modelid,123);
INSERT INTO models_cities (model, city) VALUES (modelid,456);
INSERT INTO models_cities (model, city) VALUES (modelid,789);

SELECT model.* FROM model WHERE model.model_id = model_in_city.model_id AND model_in_city.city_id = city.city_id AND city.city_name = "Toronto"
SELECT model.* FROM model WHERE model.model_id = model_in_city.model_id AND model_in_city.city_id = city.city_id AND city.city_name = "Chicago"
SELECT model.* FROM model WHERE model.model_id = model_in_city.model_id AND model_in_city.city_id = city.city_id AND city.city_name = "New York"

Will then all return the same model

Edit: well, insert using your actual table and column names i just wrote those inserts quickly to show you.

[redwhiteandblue]

select * from model, city
left join model_in_city on model.model_id=model_in_city.model_id
left join city on city.city_id=model_in_city.city_id

[eMonk]

Originally Posted by k0nr4d

You would insert multiple models_cities entries per model.

So lets say Toronto = 123
Chicago = 456
New York = 789

INSERT INTO models_cities (model, city) VALUES (modelid,123);
INSERT INTO models_cities (model, city) VALUES (modelid,456);
INSERT INTO models_cities (model, city) VALUES (modelid,789);

SELECT model.* FROM model WHERE model.model_id = model_in_city.model_id AND model_in_city.city_id = city.city_id AND city.city_name = "Toronto"
SELECT model.* FROM model WHERE model.model_id = model_in_city.model_id AND model_in_city.city_id = city.city_id AND city.city_name = "Chicago"
SELECT model.* FROM model WHERE model.model_id = model_in_city.model_id AND model_in_city.city_id = city.city_id AND city.city_name = "New York"

Will then all return the same model

Edit: well, insert using your actual table and column names i just wrote those inserts quickly to show you.

Ah thanks bro. I'll try this tonight or tomorrow. I was thinking along the lines of:

VALUES (modelid, 123, 456, 789, ...);

Which you can't do from my understanding (multiple values in column). I searched google and my book for reference but couldn't find anything on this. Thanks again!

[KillerK]

You could have one column called like cities_in or something
and then store it as

14|29|30

then just explode the value from that, however that's not the right way to do it.

You need to use multiple tables.

[mafia_man]

k0nr4d is right. Multi-valued attributes are bad.

You need to normalise your data. It will help performance when these tables get huge.

[bbobby86]

Originally Posted by redwhiteandblue

select * from model, city
left join model_in_city on model.model_id=model_in_city.model_id
left join city on city.city_id=model_in_city.city_id

also can try inner join...

[Davy]

Sounds like a typical N:M relationship. That is best modelled with another database table that connects the two other tables.

[redwhiteandblue]

Originally Posted by Davy

Sounds like a typical N:M relationship. That is best modelled with another database table that connects the two other tables.

Yes like the table "model_in_city" that he describes in his OP, which you would query with the SQL I wrote, although I would have called the table something like "model_city_rel" to make it obvious what it's for.

[Tempest]

What k0nr4d said. And make sure you put an index on model_in_city like KEY idx1 (city_id,model_id)

[borked]

This is why plural/singular naming of tables is important, but often overlooked. Not important as in "shit won't work" but important in seeing at a glance relationships....

models
cities
model_city

says automatically that the models tables is where all the models are, likewise for the cities, but modelcity is a singular relationship between a model and city.

minor detail, but still important...

btw, make those tables innodb and add their relationships, so that if you ever delete a model, all her/his entries in model_city disappear automatically also... one of the great benefits of relationships.

[Tempest]

Originally Posted by borked

This is why plural/singular naming of tables is important, but often overlooked. Not important as in "shit won't work" but important in seeing at a glance relationships....

models
cities
model_city

That's interesting as I've read enough about table naming conventions and design to know that most of the more hardcore database designers will use singular names.. i.e. model, city and not models, cities... Mostly since the tables are a collection of singular elements and it's the elements that the table contains that you're interested in.

Just a small example of what I'm getting at, SELECT model.name FROM model is more "readable" than SELECT models.name FROM models.. There are also more readability issues for different languages.

At the end of the day though it comes down to your personal preferences, what engine, language etc. you're using to develop in. The most important thing is to be consistent.