best solution?

4 Nov 2005 10:57 AM

da fish & da chip via SQLMonster.com

hi there,

i need a kind of "more about this area" feature on my website to set some
links to articles about the same area or spots in this area, but im not sure
whats the best (fastes) solution for this. "area" can be a country, a state
or just a city in this case. so have a databae and a field 'location_id'
(linked to location.location_id) at some tables :

loc_state:
-state_id
-country_id
-state_long (something like 'new south wales')
-state_short (something like 'nsw' )

loc_city
-city_id
-state_id
-name

location
-location_id
-state_id
-city_id

so what do you think about this? is it ok or has someone a better solution?
the bad thing is that i have to use mysql so i cant use views :(

4 Nov 2005 11:49 AM

ML

Why don't sou use MS SQL 2005 Express?

As for the design: a good design starts with a definition of entities and
the relationships between them. Have you thought them out well?

How does your model handle some 'tricky' entities?
"Paris, France"
"Paris, Texas" or "Paris, Texas, USA"

Some modern cities are HUGE. Are your other entities related to entire
cities or city districts? Street-level, even...?

What exactly are you designing?

ML

4 Nov 2005 1:53 PM

da fish & da chip via SQLMonster.com

ML wrote:

>Why don't sou use MS SQL 2005 Express?

because it is a php/mysql system, other parts are already done so i cant
change now and this is a private project, so i dont cant spend $$$ for a ms
sql server.

>As for the design: a good design starts with a definition of entities and
>the relationships between them. Have you thought them out well?
>How does your model handle some 'tricky' entities?
>"Paris, France"
>"Paris, Texas" or "Paris, Texas, USA"

i cant see any problems here. if the city_id for paris=5, state_id texas=9
and country_id france=61, us=185 then i have 2 different location_ids in
table location:

tbl location:
loc_id - country_id - state_id - city_id
1 - 61 - null - 5
2 - 185 - 9 - 5

i only assign the loc_id to other tables.

>Some modern cities are HUGE. Are your other entities related to entire
>cities or city districts? Street-level, even...?
>What exactly are you designing?

i add some features to a travelguide - something like "find more about this
city/state/country", so i dont need a street-level. the website is
shareyourride.net , but there is still a lot to do until everything is like i
want it.

4 Nov 2005 2:25 PM

ML

FYI: MS SQL 2005 Express (SQL 2000 MSDE) is free.

If the model covers all your use cases, then I guess it's good enough. :)

ML