kutchbhi 0 Newbie Poster

I made a classified site and am concerned that my database design amd sql queries may not be optimal. Need your suggestions. This is how the site works now:

The user selects his colony, and my app shows posts made from the colony and posts made from nearby colonies.

2 of the tables are like this-

table one: colonies1; columns - id, colony_name

table 2: close_colonies; columns - colony_id, nearby_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 COLONY, using an sql query. <snipped>

//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}  " ;
        }

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 could 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 performance wise or otherwise? I mean with 50 nearby colonies, the UNION ALL will be getting too big, right ? Any way this could be improved? in terms of table design or anything

Also my mysql 'knowledge' is limited to lynda.com tutorial on Php. please recommend some book that isn't too detailed to learn such things.

Thanks

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.