Help with making the DB structure

Newbie asking..

[I Will Make It]

Hi there, after some thinking I decided to run my site using a DB.
I will be using MySql and php.

Problem: How should I think when making my database?

Lets say I have a list of places, where each place belongs to a country. Each country belongs to a part of the world.
Example: Alicante in Spain in Europe.

I want to have the possibility to put each place in a category that decides if the place is within "Shopping", "Sun", "Family", "City" and so on..

I want to be able to "get all the places in -Europe- where -category- = City.. and of course all the other options, like get all places from where country = germany, all the countries where continent = asia and so on.. you know what i mean :)!?

Well, if you understood my problem and have the time, please give me some directions on how to think when I'm creating the DB. How many tables, what to name the tables, what table-properties I need and so on..

Thanks, and regards!

[jatar_k]

your line describes it really well
>> Alicante in Spain in Europe

I like alicante, beautiful place

so alicante has a relationship with spain and also has a relationship with europe

great

alicante is a city, spain is a country and europe is a continent. So we have one line of relations

I bet that there are different services in alicante
travel, restaurants, hotels

so then I guess alicante can be broken down other ways as well. Geographic relations can be formed as well as service relations that can be broken down geographically.

think of it as a dynamic menu structure where all things relate to all other things but you need to define the how.

most of this is pretty relative to how you theme the information you are collecting. I am guessing a travel site. So what you need to do is allow people to search and cross reference data both geographically and by theme.

two relationship trees that overlap and constantly cross reference each other.

[I Will Make It]

Thank you :)
So did I get this right?
I need at least two tables?
One with the geographic, e.g
place, country, continent
and another one with more information like
restaurants, transportation, and (city? shopping? sun?) + +

?

[jatar_k]

two tables for sure, probably more

you might have

geographic locations
categories (restaurant, hotel etc)
then actual places (bob's great diner)

the place has a relationship with both a location and a category

these relationships might need their own tables but probably a column for each in your places table would work as they both will always have a value

[Rufal]

Hi,

Best way to design tables for any application is to put it initially in text. It makes you find values you didnt know you needed.

you did it pretty well in your text.
>>Lets say I have a list of places, where each place belongs to a country. Each country belongs to a part of the world.
>>I want to have the possibility to put each place in a category that decides if the place is within "Shopping", "Sun", "Family", "City" and so on..

And then check all the nouns that are in the text.
f.i.
Place, country, "part of the world", category
So the more detail you put in your description the likelihood of getting everything you need increases.

So to explode the information completely (this can be shorter)

LOCATIONS (LOCATION)
e.g. Europe, Asia, North-America, etc..
COUNTRIES (COUNTRY, LOCATION)
e.g. USA-North-America, Iceland-Europe etc...
CITIES (CITY, COUNTRY)
e.g. Reykjavik-Iceland
CATEGORIES (CATEGORY)
e.g. Shopping, Sun, Family.

And then you have the master table that handles the joining.
PLACESTOSEE(NAME, COUNTRY, CATEGORY)
e.g. madame tussaud, London, Family.

[I Will Make It]

A great thank you to both of you :)
You really helped me a lot!

I'm learning something new every day now, thanks to WW, and the great people that's here!