Database /logic / code re-design

[Andy-H]

Hi, I work as a junior web developer for a fleet tracking company, we are trying to normalise all of our data and need to completely re-design our database, which means re-programming most of the backbone of the business, and we want to get it right this time.

 

 

At the moment we have decided that location data from both GPRS and SMS based units needs to be stored in a normalised, central database and we will read/write to this via API calls (the data will probably be returned as JSON).

 

 

I want to get round to the MVC way of doing things so that extending and changing the functionality of the API in the future will be easier, but at the moment we are still designing the database.

 

 

So we will have a units table (units currently all run via SIM card (either SMS/GPRS based) but in the future we may use units that don't have telephone numbers.

 

 

Currently we have:

 

units:
unit_ID(PK_AI)
type (VARCHAR)

unit_info
unit_ID (FK)
key(VARCHAR)
value(VARCHAR)

LatLng
LatLng_ID (PK_AI)
lat (FLOAT(10,6))
lng(FLOAT(10,6))

addresses
LatLng_ID (FK)
formatted_address (VARCHAR)
street_number (VARCHAR)
street_name (VARCHAR)
town (VARCHAR)
city (VARCHAR)
postcode (VARCHAR)
postcode_prefix (VARCHAR)
country (VARCHAR)
country_code (VARCHAR)

 

Is there anything anyone can see that's wrong with this database structure?

 

<?php
/**
* LatLng
*
* This class stores a Latitude and Longitude and provides methods to 
* set / retrieve the Latitude and Longitude and compare distance, bearing etc between.
*
* @version 1.0
* @package LatLng
*/
namespace Location;

class LatLng {
   /**
   * Stores the latitude of LatLng
   * @access private
   * @var float
   */
   private $_lat;
   /**
   * Stores the longitude of LatLng
   * @access private
   * @var float
   */
   private $_lng;
   
   /**
   * LatLng::__construct
   * Sets internal latitude and longitude of LatLng object being instantiated
   * @param float $lat Valid latitude, must be between -90 and 90
   * @param float $lng Valid longitude, must be between -180 and 180
   * @access public
   * @throws Location_Exception
   */
   public function __construct($lat, $lng) {
      if ( !$this->_checkLat($lat) )
         throw new Exception('Invalid latitude passed to Location_LatLng::__construct');
      if ( !$this->_checkLng($lng) )
         throw new Exception('Invalid longitude passed to Location_LatLng::__construct');
      $this->_lat = $lat;
      $this->_lng = $lng;
   }
   /**
   * LatLng::lat
   * Gets internal latitude of LatLng object
   * @access public
   * @return float
   */
   public function lat() {
      return $this->_lat;
   }
   /**
   * LatLng::lng
   * Gets internal longitude of LatLng object
   * @access public
   * @return float
   */
   public function lng() {
      return $this->_lng;
   }
   /**
   * LatLng::distanceTo
   * Computes the distance (as the crow flies (defaults to miles)) from the location on this LatLng object to another
   * @param LatLng $LatLng LatLng object to compute distance to
   * @param int $precision Decimal precision of returned distance
   * @param string $units Units to return distance in ('m'(miles - default), 'k'(kilometers), 'n'(nautical miles))
   * @access public
   * @return float
   * @throws Location_Exception
   */
   public function distanceTo(LatLng $LatLng, $precision = 2, $units = 0) {
   
      $dist = sin(deg2rad($this->lat())) * sin(deg2rad($LatLng->lat())) +  cos(deg2rad($this->lat())) * cos(deg2rad($LatLng->lat())) * cos(deg2rad($this->lng() - $LatLng->lng())); 
      $dist = acos($dist); 
      $dist = rad2deg($dist); 
      $miles = $dist * 60 * 1.1515;
      if ( !$miles || strtolower($miles[0]) == 'm' )
         return round($miles, (int)$precision);
      if ( strtolower($units[0]) == 'k' )
         return round($miles * 1.609344, (int)$precision);
      if ( strtolower($units[0]) == 'n' )
         return round($miles * 0.8684, (int)$precision);
      throw new Exception('Invalid parameter(3) units identifier passed to Location_LatLng::distanceTo, "m"(miles), "k"(kilometers) or "n"(nautical miles) expected');
   }
   /**
   * LatLng::bearingTo
   * Computes the bearing from the location of this LatLng object to another
   * @param LatLng $LatLng LatLng object to compute bearing to
   * @param int $precision Decimal precision of returned bearing (default 0)
   * @access public
   * @return float
   * @throws Location_Exception
   */
   public function bearingTo(LatLng $LatLng, $precision = 0) {
      return round((rad2deg(atan2(sin(deg2rad($LatLng->lng()) - deg2rad($this->lng())) * cos(deg2rad($LatLng->lat())), cos(deg2rad($this->lat())) * sin(deg2rad($LatLng->lat())) - sin(deg2rad($this->lat())) * cos(deg2rad($LatLng->lat())) * cos(deg2rad($LatLng->lng()) - deg2rad($this->lng())))) + 360) % 360, (int)$precision);
   }
   /**
   * LatLng::_checkLat
   * Checks if $lat is float with value between -90 and 90
   * @param mixed $lat Variable to check is valid latitude
   * @access protected
   * @return boolean
   */
   protected function _checkLat($lat) {
      return ( is_float($lat) && $lat >= -90 && $lat <= 90 );
   }
   /**
   * LatLng::_checkLng
   * Checks if $lng is float with value between -90 and 90
   * @param mixed $lng Variable to check is valid longitude
   * @access protected
   * @return boolean
   */
   protected function _checkLng($lng) {
      return ( is_float($lng) && $lng >= -180 && $lng <= 180 );
   }
}

 

 

Also I have written this class (not tested it) but should it include a save method (to insert the latlng into the database) and option to be instantiated using an ID?

 

 

Should I even be storing LatLng's separate from addresses and other Location information (such as inputs, speed, satellites available etc) or are they going to change that rarely that I should just store them as an inbound location?[/code]

[ignace]

Is there anything anyone can see that's wrong with this database structure?

 

If units and units_info have a 1:1 relation then I would keep them in one table. Same goes for your LatLng and Address tables, these have, as I see it, a 1:1 relation (unless two different addresses can share a lat/lng?*) otherwise add lat, lng to the Address table and create an Address ID field.

 

Further possibilities could be: give city and postcode their own table. Country too. Maybe even street_name too? It depends on your needs.

 

Also I have written this class (not tested it) but should it include a save method (to insert the latlng into the database) and option to be instantiated using an ID?

 

No. But that's me. I would probably pass the Address entity to a Repository (or a DataMapper at minimum) to insert it into the database.

 

(*) You could argue that an apartment block shares a lat/lng for all it's tenants but a GPS doesn't route you to the 7th floor, right? And since for a row in the address table to be unique the street_number has to be different meaning the location is different also means the lat/lng are different.

[Andy-H]

Is there anything anyone can see that's wrong with this database structure?

 

If units and units_info have a 1:1 relation then I would keep them in one table. Same goes for your LatLng and Address tables, these have, as I see it, a 1:1 relation (unless two different addresses can share a lat/lng?*) otherwise add lat, lng to the Address table and create an Address ID field.

 

Further possibilities could be: give city and postcode their own table. Country too. Maybe even street_name too? It depends on your needs.

 

Also I have written this class (not tested it) but should it include a save method (to insert the latlng into the database) and option to be instantiated using an ID?

 

No. But that's me. I would probably pass the Address entity to a Repository (or a DataMapper at minimum) to insert it into the database.

 

(*) You could argue that an apartment block shares a lat/lng for all it's tenants but a GPS doesn't route you to the 7th floor, right? And since for a row in the address table to be unique the street_number has to be different meaning the location is different also means the lat/lng are different.

 

 

Yeah, I noticed the thing with the latlng/address earlier, multiple LatLng's can refer to a single address (I updated the one-to-many relationship to reflect this). The idea of the key / value pair for unit_info is that different units will have different information stored about them, for example a GPRS unit may store an IP address whereas an SMS unit will store a telephone number / IMEI number etc. (although this isn't the case but my boss says there is another type of unit we may use in the future), I have added an alias field to the units table to accommodate this (and allow selection via a human-readable field).

 

 

The idea is that LatLng will be extended by GPS_location and each will have a collection class implementing the Iterator interface, then you can pull journeys and select what extent of data you wish to retrieve, i.e. just a LatLng collection or a GPS_location collection (for infoboxes on marker clicks and such).

 

 

I like the country and postcodes having their own separate table, that one slipped by me but I see the advantage now. The addresses are from the Google reverse geocoding API so the data is extremely difficult to normalise but the address table contains the possible data that could be returned, but we have locations in Nigeria where all we receive is country and street name (I think this is the minimum).

 

 

As for the Repository and DataMapper, I have not heard of these design patterns but will look into them and check back tomorrow, thanks for the advice ignace

[Andy-H]

Just thinking, should my GPS_location class extend LatLng, it does have a longitude and a latitude, so the operations defined in LatLng will still be applicable, however the data is not directly related, as in a GPS location has a latlng, but the other data is not geographic, it will be bearing, speed, inputs etc. so this leads me to think I should simply inject the GPS_location constructor with a LatLng object, also this API will only output the data in JSON format (as far as I know at the moment), so is this necessary?

 

 

Also; if I use PDO::fetchAll() to retrieve a list of latitude and longitudes, then I have to loop through the data to create the LatLng objects, when in actual fact I don't need the behaviour of the object, I simply need to output the latitude and longitude as part of a JSON string, what would be the best way to go about this?

[Andy-H]

New database design.

[ignace]

The idea of the key / value pair for unit_info is that different units will have different information stored about them, for example a GPRS unit may store an IP address whereas an SMS unit will store a telephone number / IMEI number etc.

 

It's an EAV-model and it has disadvantages you should be aware of before using.

 

I'll reply later on the rest of your posts when I have time.

 

[Andy-H]

OK; so I should have a main table for common unit data and then two tables, one for data unique to SMS devices and one for GPRS devices? Which way should I join these tables? I'm thinking that if I join back from SMS_unit to unit and the same with GPRS unit, when selecting; the only way to retrieve which type of unit it is would be to left join both and differentiate via null values, this leads me to think that I should join from unit to SMS_unit/GPRS_unit but am unsure which of these methods is the "way to go".

 

 

I assume my application should be aware of the different types of unit but I want this to be abstracted out from the user/request layer, although saying that, I realise that SMS units will not implement a get journey method whereas GPRS units will; I am really stuck with this one?

 

 

Here is a brief of what we want to achieve:

 

 

- Store locations/data for SMS and GPRS units in a centralised data-store

- Allow an easy method to retrieve journeys and related data on GPRS units

- Store administration / location logs (such as location request, location response; respectively (the administration logs aren't necessarily 1-1 relationship and not necessarily in order of request/response i.e. an alarm can be triggered in a unit and send a response to the server, which we need to log, we can validate this via a unique message id))

- Create a users system where a super-admin can add units and users and give users permission to view units, that user can then add users and units and give it's users access to it's subset of units, in turn the super admin will be able to act on any units created in this tree

- Create an API where users can authenticate and query (CRUD) units they have access to for locations/journeys, administrate their users and such

 

 

So far I have created the database as shown above, I have also added a users table (attached), this joins back to itself, with a parent (ID of the user that created the user), level (the level of the user that created the user +1 or null for level 0), I will then expose functionality to users based on their level, it will only go to level 3.

 

 

Level 0 user - Can add devices/users and give permission for users to view a subset of devices, can administrate it's users and access any devices an y branch of it's tree ads. (this is basically our administrative (su) account.

Level 1 user - Same as above

Level 2 user - Same as above

Level 3 user - Can only act upon devices assigned to it, can't add new devices or users

 

 

The reason for the replicated functionality in the level 0, 1, 2 users are so we can create super-user's for other companies using our application, whilst in turn giving them the ability to create administrators/add devices, I think I have missed a trick somehow here though as I know replicated functionality is not a good thing.

[Andy-H]

Reading through the last post I realise that I can remove a level of user depth by giving users the ability to create users at the same level as themselves, then to differentiate between the top 2 levels (not just for the sake of it), I can allow all level 0 users to administrate other level 0 users devices.

[ignace]

OK; so I should have a main table for common unit data and then two tables, one for data unique to SMS devices and one for GPRS devices? Which way should I join these tables? I'm thinking that if I join back from SMS_unit to unit and the same with GPRS unit, when selecting; the only way to retrieve which type of unit it is would be to left join both and differentiate via null values, this leads me to think that I should join from unit to SMS_unit/GPRS_unit but am unsure which of these methods is the "way to go".

 

Well, you could for example just create these as columns on the units table and differentiate between them using a type (GPS or SMS) column. Another possible approach is to have a units table, and gps_units and sms_units tables that reference the PK in the units table. The first approach has the advantage that you don't have to do any join's but if you know on forehand on which table you'll need to join (eg, because you know it's GPS) you can use the second approach.

 

And if you do insist on using EAV then look at MongoDB or similar NoSQL alternative.

[Andy-H]

Are there any advantages to NoSQL over a RDBMS? As far as I know we want an RDBMS but if there's any other reason MongoDB, or other NoSQL implementations, would be an advantage in this scenario I don't see a reason not to consider it. Otherwise I'll just remove the EAV table. Thanks again.

[ignace]

Both NoSQL and RDBMS have their (dis-/)advantages.

 

I for example use it when I load my objects from a DB using a DataMapper I load them with ALL their data, serialize them, and store them in CouchDB instead of mapping them every time. The hard part is when an entity changes, you have to update both CouchDB and the DB (and any other app that uses this DB, though there are plugins for MySQL that allow you to put/post/delete to CouchDB I believe). When it can't find the entity in CouchDB it starts the mapping/caching process. The Repository queries CouchDB mapper first before querying the DB mapper. When an entity is inserted/updated/deleted it updates both CouchDB and the DB through it's respective mapper.

 

I am only experimenting with this though.

[ignace]

When you are talking about units do you then mean vehicles?

How does SMS fit in this story? (The Driver texts the system with their position? )

What does a Journey constitute of? Checkpoints?

[Andy-H]

Both NoSQL and RDBMS have their (dis-/)advantages.

 

I for example use it when I load my objects from a DB using a DataMapper I load them with ALL their data, serialize them, and store them in CouchDB instead of mapping them every time. The hard part is when an entity changes, you have to update both CouchDB and the DB (and any other app that uses this DB, though there are plugins for MySQL that allow you to put/post/delete to CouchDB I believe). When it can't find the entity in CouchDB it starts the mapping/caching process. The Repository queries CouchDB mapper first before querying the DB mapper. When an entity is inserted/updated/deleted it updates both CouchDB and the DB through it's respective mapper.

 

I am only experimenting with this though.

 

 

Sounds good, what I understand of it anyway; so basically you cache results from an RDBMS in a NoSQL datastore because NoSQL retrieves the results faster? Then commit the data back to your RDBMS? I want to avoid using NoSQL because I don't want to be this guy -->

 

 

And referring to SMS what I actually mean is GPS over SMS tracking units that send us data only when requested or triggered to do so by an input (wire) from a caravan alarm or motion sensor. This could actually be a GPRS based unit but I'm used to calling units that function in this way SMS units, just because that's what my boss calls it, but these devices are simply for recovering stolen vehicles.

 

 

A GPS over GPRS tracking unit sends us data every X seconds when the vehicle ignition is on (this is for fleet tracking and such), it also sends us extra data such as ignition state(0/1), odometer and such that "target"(SMS) units don't send us - this is why I said SMS units don't require a get journeys method.

 

 

At the moment we don't need to retrieve data for GPRS and SMS units in the same request, but I know if I leave that functionality out someone will end up buying a fleet unit and a target unit and want to display them on the same interface whether it's a month or a year down the line, it's the "law of sods" lol

 

 

A journey consists of any location between an ignition on and an ignition off (checkpoints as you said I suppose).

 

 

Take a look at http://demo.phantominsight.co.uk this is the current fleet tracking system we've made, it only supports fleet units / GPRS, it's gotten quite slow recently but I can't figure out why, it seems to be when you first follow a vehicle, maybe loading the arrow images.

[Andy-H]

Just thinking, should my GPS_location class extend LatLng, it does have a longitude and a latitude, so the operations defined in LatLng will still be applicable, however the data is not directly related, as in a GPS location has a latlng, but the other data is not geographic, it will be bearing, speed, inputs etc. so this leads me to think I should simply inject the GPS_location constructor with a LatLng object, also this API will only output the data in JSON format (as far as I know at the moment), so is this necessary?

 

 

Also; if I use PDO::fetchAll() to retrieve a list of latitude and longitudes, then I have to loop through the data to create the LatLng objects, when in actual fact I don't need the behaviour of the object, I simply need to output the latitude and longitude as part of a JSON string, what would be the best way to go about this?

 

 

Just realised that PDO has functionality similar to mysql_fetch_object, so I can call

 

 

PDOStatement::fetchAll(PDO::FETCH_CLASS, 'LatLng');

[ignace]

Sounds good, what I understand of it anyway; so basically you cache results from an RDBMS in a NoSQL datastore because NoSQL retrieves the results faster?

 

Don't know about faster, but it skips the whole mapping (from table rows to objects) process.

 

And referring to SMS what I actually mean is GPS over SMS tracking units that send us data only when requested or triggered to do so by an input (wire) from a caravan alarm or motion sensor.

 

A GPS over GPRS tracking unit sends us data every X seconds when the vehicle ignition is on (this is for fleet tracking and such), it also sends us extra data such as ignition state(0/1), odometer and such that "target"(SMS) units don't send us - this is why I said SMS units don't require a get journeys method.

 

But this includes only vehicles, right? And this is only for stolen vehicles? And these updates are they send to some PHP script or stored into the db from another source?

 

[Andy-H]

 

Well, we only apply it to vehicles at the moment, but if we find a unit with decent enough battery life, it may be applied to animals etc.

 

Not just stolen vehicles, we're more likely to query a target units location if the vehicle it's attached to is reported stolen, but we query them all on a regular basis to test whether they're still working.

 

 

The fleet units are attached to all kinds of vehicles, vans, trucks, JCB's etc. they just allow a business track their employees on a google map for whatever reason.

 

 

The data is inserted via

 

 

SMS units send an SMS to a gateway which pipes the data via HTTP to a script that inserts the data into a MySQL database.

 

 

The GPRS units send packets to a server we have set up that parses the data and inserts it into a MySQL database.