I wrote a software for a customer that shows clients on a map (Google Maps).
I store the clients on a table on the MySQL database (table
clients) and show them on the map using markers. Each clients has an id, name, lat, lon, and other satellite fields.
The customer wanted to add a feature to add notes to the map.
I made a second table (
notes). Each note has an id, text and lat,lon.
The application now makes two requests to populate the map, one to get the clients, the other to get the notes. It works very well but now I'm struggling to implement a "search nearby" search on the map. Before the notes I could simply make a MySQL query on the clients sorting them by distance from a given point on the map. Now I should make two queries, but clients and notes could be interleaved:
- client12 - 200m
- client8 - 2km
- note10 - 5km
- note3 - 8km
- client1 - 12km
Since I'm using LIMIT/OFFSET on the MySQL query to limit the items returned on each call (the call is reiterated with a different OFFSET if the user scrolls down the list), I don't really know what could be the best approach to combine the two types of items.
Here some options I could think of:
Intereaving in software after the database queries (sounds a really bad idea. Also must give up the LIMIT/OFFSET part of the query)
notestables and use a single
itemtable. Notes now have a lot of NULL fields because clients have a lot of very specific fields not relevant for the notes