This won't scale, that's how I started 7 years ago with such an application doing vehicle tracking.
I have 3500 of those assets now, imagine them inserting 1 record at the same time. This was a nightmare, especially when GPRS comms failed at provider level and all those updates where waiting, every device has 50 records to send....
We now keep all data in sqlite files per imei, and it is not served with an http servers (although it speaks http) but with a custom TCP server. The reason is:
- scalability together with haproxy
- data overhead over GPRS costs money. So every server header we needlessly send back will end up on our GSM bill.
You should seperate your logic (e.g. customers, devices) and everything that drives your site away from where the events of the trackers end up.
Here's a basic schema that scales (sqlite file PER unique imei):
CREATE TABLE Event_customerx_all (
record_id INTEGER PRIMARY KEY,
com_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
gps_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
imei bigint(20) NOT NULL,
switch unsigned int(3) NOT NULL,
event_id unsigned int(5) NOT NULL,
latitude unsigned int(10) NOT NULL,
longitude unsigned int(10) NOT NULL,
IO unsigned int(3) NOT NULL,
raw_data BLOB NOT NULL,
sys_parsed int(3) DEFAULT NULL,
UNIQUE (imei,raw_data) ON CONFLICT IGNORE
And it's simple in design. raw_data is 33 bytes of our events, this is a custom compresion, every package is that large, max of 42 events can be sent (the device uses GET method).
Using a custom class you can extract more information like gps sats in view, or fixed sats, distance, speed .... etc but you won't really need to 'unpack' it all when it comes in. The other data (event_id, switch, gps_date etc) is actually also extractable in raw_data. You unpack when you need it.
now why is this good? well, this scales, imagine you invent a new var, now you need to change the schema when you 'extract' them all. But in this case, your software takes care of it, as your class will be able to extract that event detail.
You application will basically always want to do in (general) for the most part:
- Select either the latest of information
- Select a date range (e.g. trip/track).
Most will fall under these 2. So you also keep your latest position (and perhaps latest interesting event like start/stop, as I do ...) separately when it comes in.
In this app, switch and event_id will make you able to figure out what type of raw_data you have, as some events don't have coordinates, you'll want to filter those when drawing a map, perhaps not if you are creating a report....
Do not write a trigger for this... Very bad idea. What I explain above has replaced such a design. And all those 3500 clients are being received by single a 8Gb Linode VPS.
There's much more, but not making this mistake will gain you years if this is a serious application we are talking about. You're welcome ;-)