Is this a good SQL DB schema?

Status
Not open for further replies.

krazyjosh5

theres GOLD in dem tubes!
May 28, 2007
1,010
19
0
Hey all,
I have a shop locator and here are the tables in it.

Is this a good SQL DB schema? It just seems like in order to pull up a shop, I need to make so many calls (I pull the shop, then I gotta pull the city ID, then, from that, I gotta pull the state ID for EVERY record.)

Is there any way this could be optimized? Should I optimize it into one table with all the info for every shop on each line? If so, any recommendations to quickly do this?

Thanks!

Oops, heres the image
2002019532123980729_rs.jpg
 


The way you're doing it is generally the 'right' way to do it.

Instead of focusing on the PITA of writing the multiple JOIN sql statement, look at how this design can help you.

For example, if you pull up a page that needs a select dropdown for 'state', you just 'select * from state order by state asc' and iterate thru the resultset.

Further optimizations could be to pull 'type' and 'address' out to seperate tables, but that may be overkill for your application. Most implementations i've seen have an address table which has something lik 'id', 'address', 'city', 'state' and 'zip'.

Note: from re-reading your post, it seems you don't understand sql join statements. To pull your recordset in a single call, you can do:

select * from shops sh, cities c, state st
where
sh.city_id = c.id
and
c.state_id = st.id
 
  • Like
Reactions: krazyjosh5
i'd combine city and state if it was a small number of records - but if you've got hundreds of cities in each state...
 
yeah ive yet to venture into join statements. afaik, sql is all pretty easy and then join is the only thing you need to take a few to wrap your head around.

thanks for the help shaggz, +rep
 
It helps to think of the relationships. Currently this is how they read.

Shops & Cities:
Shops must have only one city.
Cities have one, many or no shops.

Cities & States:
Cities must have only one state.
States have one, many or no cities.

Is that what you're trying for? Writing a join to pullup the city and state information for a particular shop is not difficult nor very time/resource consuming.

Are you planning on displaying large lists of shops on a single page? If so, how many?
 
I couldnt tell you exactly because this is a directory of shops and I dont know how many shops would be in big cities. Since its going to be a mobile application, per page, im thinking 5.
 
I couldnt tell you exactly because this is a directory of shops and I dont know how many shops would be in big cities. Since its going to be a mobile application, per page, im thinking 5.

If that's the case you'll be fine as far as query times go. One well written join combined with proper use of indices on the tables will be super fast for querying 5 shops (or 50).

One thing to consider is the reality of a city being in more than one state.

There are a lot of different "Springfield"'s in various states.
 
One thing to consider is the reality of a city being in more than one state.

There are a lot of different "Springfield"'s in various states.

Though if the database is queried using the id fields as primary and foreign keys, the name really shouldn't matter. That the town is called 'Springfield' should be a cosmetic detail only.

But yeah, worth considering, especially if you were planning on using the town name as a query term.
 
gotcha, just so i know for sure, can someone tell me:

does this script create a new database connection every time a search is run on it? (ie every time i select a state and hit submit)

PHP:
<?php
$hostname = "scrubbed";   
$username = "scrubbed";   
$password = "scrubbed";   
$database = "scrubbed";   
$link = mysql_connect($hostname,$username,$password);
mysql_select_db($database) or die("Unable to select database");
$userState = $_POST["state"];
$query = mysql_query("SELECT name FROM shops sh, cities ci, states st WHERE sh.city_id = ci.id AND ci.state_id = st.id AND '$userState' like abb");
while($array=mysql_fetch_array($query)){
echo $array['name'];
echo "<br>";
}
?>
 
and if so, how can i cut down on the number of connections? i have dreamhost and they are bitchy about using more connections than you need to. according to their point system, a connect is 25 points and a query is 1.
 
Status
Not open for further replies.