Database Design

[eMonk]

Hello everyone!

 

I'm working on my first php/mysql project and need some guidance how I should structure this database. It will be for a modeling site.

 

- on the main page a list of cities and provinces for the users to choose from.

 

- once a city is chosen, a list of models from that area will be displayed in thumbnails.

 

- once a model is picked from the city list, her bio page will appear with the following info:

    - age

    - height

    - hair

    - measurements

    - eyes

    - description

 

The main page will also include a search feature and pagination so they can display 15-30-45-etc thumbs at a time. The models can also choose up to 4 cities to be placed it so I would need to have multiple values in the city entry.

 

Any idea how I can structure this? I was going to put these in 1 table but read that's a bad idea and can't think how I would do this using multiple tables and need some advise. Thanks in advance!

[ignace]

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)

[eMonk]

Ah that makes sense. Thanks ignace.

 

How would you do multiple city values (up to 4) in the model_in_city table?

[ignace]

You can't enforce any hard constraint at table-level but you can do that at the application-level. You could use a trigger ON BEFORE INSERT though.

[fenway]

Don't store multiple values in a single field.

[ignace]

You can't enforce any hard constraint at table-level but you can do that at the application-level. You could use a trigger ON BEFORE INSERT though.

 

My mind tricked me

 

Just to be clear: you add models to cities like:

 

INSERT INTO model_in_city (model_id, city_id) VALUES

(1,1), (1,2), (1,3), (1,4);

 

Places model 1 in cities 1,2,3, and 4.