Database schema for two different types of elements on map

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.