I need to create a vehicle tracking database. There are several vehicles that send data, consisting of around 20 parameters, to my database.
The parameters of a vehicle include the constants vehicle number and hardware id, along with a variety of variable items.
I will have a table in this database containing the login credentials of the users and I would like to create a link between the two tables with the vehicle number as primary key in the login table and with the vehicle number in table containing parameters.
Since the vehicle number column is redundant I cannot have it as the primary key.
I would like to know:
- What way can I achieve the link between tables
- Is there a good way so that I dont store this vehicle number and hardware id so many times.
- Should I be storing each vehicle's data in separate table rather than all in one table?
This is the first time I am designing a database. I have only been a programmer to date.
Best Answer
I expect this model to be a bit more complicated than two tables. If the vehicle tracking provides you with two IDs, then they may probably be foreign keys to some specification. Also, it is entirely possible that different vehicles will have the same hardware, and the other way around, different hardwares will have the same vehicle. In that case, you need to normalize it by placing in different tables.
My proposition is based on the following model:
EDIT
Thanks to @DamirSudarevic I've made some updates in the model.