Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

  1. What way can I achieve the link between tables
  2. Is there a good way so that I dont store this vehicle number and hardware id so many times.
  3. 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.

share|improve this question
1  
this was cross-posted on SO stackoverflow.com/questions/12457367/… –  bluefeet Sep 17 '12 at 10:33
    
Could you provide an English description of the tables and what the link represents? –  Emmad Kareem Sep 17 '12 at 12:52
    
@bluefeet The SO post has been closed because of the Xpost. –  JNK Sep 17 '12 at 14:31
    
@JNK I saw, thanks. :) –  bluefeet Sep 17 '12 at 14:34
add comment

2 Answers

up vote 4 down vote accepted

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:

Vehicle system model

EDIT

Thanks to @DamirSudarevic I've made some updates in the model.

share|improve this answer
1  
Tracking must point to Vehicles_Hardware FK {vehicle_number, hardware_id}. The way it is now, it allows for any combination of vehicle_number, hardware_id even if they are not paired together. –  Damir Sudarevic Sep 18 '12 at 12:00
1  
Also User_Cars and Vehicles_Hardware, both FKs should be part of a PK, -- the way it is now, both tables allow duplicate entries. –  Damir Sudarevic Sep 18 '12 at 12:17
    
In both comments, you are right. I'm just unable to correct the answer at the moment, I'll do it later on... –  WojtusJ Sep 19 '12 at 7:56
add comment
  1. You create the link between the tables with a UserVehicles table containing foreign keys to the Vehicles and Users tables.

  2. The HardwareId would never be stored more than once. The VehicleNumber would be stored multiple times only when necessary to show the relationship.

  3. No, each vehicles data should be in the same table.

Your design will probably look something like this:

Vehicles
=============
VehicleNumber
HardwareId
Color
Style
Make
Model
Seats
Engine
...


Users
==============
UserId
Username
Password
FirstName
LastName


UserVehicles
===============
UserId
VehicleNumber

Re-reading your question I'm wondering if by "vehicles keep sending data" you are tracking some sort of values that are fluctuating rather than attributes of the vehicle. If this is the case you may need something like this (substituting each stat with a specific name and well defined type):

 VehicleStats
 ===============
 DateTime
 Stat 1
 Stat 2
 Stat 3
 ...
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.