I am considering whether I should add columns to my existing tables or use a new table. The database is about birds, there locations over time and there nesting locations.
The data that I need to store is point (X,Y) location data. This data will come from a GPS, and while two locations may be very close to each other I think it is very unlikely to get the same X,Y twice, even if you think you are standing in the same spot.
First table is called health check (and each health check is related back to a bird, and a bird has many health checks over its life). Each time a health check is done the location and date is recorded.
The second table is called Nest - each nest has a single location. The nests are immutable - ie they don't move.
How will this data be queried?
The health check location of each bird (X,Y) will be (queried) plotted over time to see where a bird has been moving.
The nest locations will be plotted all at once (ie a query to get all nest locations and plot them). Quite often, maybe 50% of the time a birds location during a health check will be the location of the nest from the nest table.
The number of birds involved is about 300. The db is MySQL.
Should the location (X.Y) data go on its own table?
I can provide more information if needed.