I am building a fleet unit management software which handles data from GPS devices mounted on the clients' vehicles.
Now i have to implement the database design. I have two main tables.
table 1: Fleet_Unit(unit_id, col_2, col_3, col_4), unit_id is PK
table 2: Gps_Device(device_id, col_2, col_3, col_4), device_id is PK
In most scenarious only one gps device is mounted on one vehicle but there are cases when two or even more gps devices may be mounted on the same vehicle for various reasons. So i have to decide if i should have an extra table to map the vehicles to the gps devices or put an extra column inside the Gps_Device table where the ID of the Fleet_Unit table will be stored.
I decided to use the first approach so i made one more table:
So before i ask you for opinion let me introduce you few more tables that are used to describe the fleet_unit object.
Each gps device has some analog and digital I/O. There may be one or more fuel meters connected to the gps device and vehicle's engine, temp sensors, card readers and so on.
So here is ONE of the extra tables (the others are similar):
fleet_unit_digital_sensors_mapping(fleet_unit_id, gps_device_id, sensor_col_1, sensor_col_2)
As i mentioned there may be more than one gps device mounted on the same vehicle so digital-sensor-1 which is read from first gps device is different from digital-sensor-1 which is read from the second gps device.
Now i'd like to ask you how to implement the database.
Should i use fleet_unit_id and gps_device_id from table fleet_unit_digital_sensors_mapping as foreign keys for tables Fleet_Unit and Gps_Device
create a new column master_id which is "combination" of fleet_unit_id and gps_device_id and connect it to the mapping table - Fleet_unit_gps_device_mapping where i need to introduce another column used for the map.
The reason I'm asking is because in first scenario if i have 10+ tables that describe the vehicle i have to connect each one with the fleet_unit and gps_device table. In second case i simply connect all 10+ tables with the table that maps the vehicle and the gps devices.
Thanks in advance.