Need help with the design of a couple of tables

kutchbhi · #1 (**permalink**) 12-25-11, 06:43

I made a classified site, this is how it works now:

People select a location(colony) and make ad posts. Then a user selects his colony, and my app shows posts made from the colony and posts made from nearby colonies. Simple.

Quote:

2 of the tables are like this-

table one: colonies1; columns - id, colony_name

table 2: close_colonies; columns - colony_id, close_colony_id

there is a foreign key relationship between 2 colonies. Each colony has 4-5 nearby colonies . There could be 2000-10000 colonies, in the first table.

to fetch the ad posts, i use the following SQL query:

Code:

//FIRST GET $close_colonies_ids  BASED ON SELECTED COLONIES, using an sql query.

//GET AD POSTS
foreach($close_colonies_ids as $colony_ids)     // get each colony id and combine them in a long sql query to make one large $resultset
        {
            $query .= " UNION ALL SELECT id FROM city_items WHERE colony_id={$colony_ids} " ;
        }
$query .=  " order by time_stamp ASC limit 20 " ;

city_items is the table with all of the ad posts.

Now I want to add something to the site:the user would select a colony, and a radius. And the site would show him all posts made from colonies within that radius. the table would then look like :

Quote:

table one: colonies; columns - id, colony_name

table 2: close_colonies_500meters; columns - colony_id, close_colony_id

table 3: close_colonies_1000m; columns - colony_id, close_colony_id

and so on... In this case depending upon the distance , each colony could have 3-50 nearby colonies.

Now is there anything wrong with this setup design wise or otherwise? I mean with 50 nearby colonies, the UNION ALL will be getting too big, right ? I know the sql query can be improved a LOT, but thats for later, right now I am concerned whether the table design is okay or not. Would really appreciate an answer.

Thanks

r937 · #2 (**permalink**) 12-25-11, 09:55

there are two improvements i would suggest

first, you don't need a separate SELECT in the union query for each colony

you can replace this --

Code:

SELECT ... FROM ...  WHERE colony_id = 3
UNION ALL
SELECT ... FROM ...  WHERE colony_id = 9
UNION ALL
SELECT ... FROM ...  WHERE colony_id = 37

with this --

Code:

SELECT ... FROM ...  WHERE colony_id IN ( 3 , 9 , 37 )

secondly, you don't need multiple tables for different distances, you can do that with a single table and a distance column, and then do

Code:

SELECT close_colony_id 
  FROM close_colonies
 WHERE distance < 1000

instead of querying both the "500" and "1000" tables

Internet Services

Web Development

Digital Marketing

Consumer Tech