long fields for both
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 (
notes tables would also still be required (without their respective
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
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
lon fields for both
notes, so the distance query which sorts by distance would only need to care about a single table.
The other two tables
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
(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
notes entry, but that generally isn’t a problem because those fields wouldn’t have any non-key dependencies in the underlying database.