Database schema for two different types of elements on map

Question

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:

e.g.

• 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)

• Remove clients and notes tables and use a single item table. Notes now have a lot of NULL fields because clients have a lot of very specific fields not relevant for the notes

Answer 1

The lat and long fields for both clients and notes seem to represent the same columns duplicated across multiple tables, which is a violation of the DRY principle, so those columns should be consolidated into a single table (item?).

However both clients and notes tables would also still be required (without their respective lat/long fields which are in the separate item table) to adhere to 3rd Normal Form (3NF). That is to say that you should avoid the approach of combining those fields which are only meaningful for either clients or notes into a single table by using NULL, since that would imply dependencies between non-key attributes, which is a violation of 3NF.

Recommended reading on SO: https://stackoverflow.com/questions/723998/what-are-database-normal-forms-and-can-you-give-examples

---

Update: Just to clarify - the item table would contain lat and lon fields for both clients and notes, so the distance query which sorts by distance would only need to care about a single table.

The other two tables clients and notes would only be needed to select additional fields which are specific to those entities; so for example, if the distance query also needed to include the [clients].[name] and [notes].[text], then the query could include a LEFT JOIN to pick up those fields where applicable - for example:

SELECT i.id, i.lat, i.long, c.name, n.text FROM item i LEFT JOIN clients c ON c.itemId == i.id LEFT JOIN notes n ON n.itemId == i.id ORDER BY ... 

Naturally a LEFT JOIN means that the query results will contain NULL fields depending on whether the item is joined to a clients or notes entry, but that generally isn't a problem because those fields wouldn't have any non-key dependencies in the underlying database.