Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2011

    Unanswered: Need help with the design of a couple of tables

    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.

    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:

    //FIRST GET $close_colonies_ids  BASED ON SELECTED COLONIES, using an sql query.
    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 :
    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.


  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    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 --
    SELECT ... FROM ...  WHERE colony_id = 3
    SELECT ... FROM ...  WHERE colony_id = 9
    SELECT ... FROM ...  WHERE colony_id = 37
    with this --
    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
    SELECT close_colony_id 
      FROM close_colonies
     WHERE distance < 1000
    instead of querying both the "500" and "1000" tables | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts