relaciones y llaves foraneas en bd

[andresgerard]

Buenos dias amigos, vengos a hacerles una consulta, estoy desarrollando un sistema para rastreo satelital de vehiculos, como proyecto de grado, y con nocines basicas que tengo de programacion php y mysql logre hacerlo, pero me encuentro en el punto de lo que es definir metodologicamente el proceso. Estoy en la parte del diseño conceptual, y consulte con un profesor la estructura de la bd que tengo actualmente y casi que me bota del aula. Se las quiero mostrar a ver si me dan alguna opinion con respecto a ella y si tienen algun consejo para mejorarla, se los agradeceria.

El sistema utiliza 4 tablas, 2 utilitarias y 2 en constante registro y consulta. Una tabla llamada usuarios que es donde se almacena los datos de los usuarios a ingresar, otra vehiculos, que igualmente como la de usuarios se almacena los datos de los vehiculos, una tabla llamada inbox, que es donde se almacenan las actualizaciones de los vehiculos y otra que es posiciones, la cual sirve para guardar los registros refinados de la tabla inbox.

A la tabla Inbox llegan los SMS, el texto del mensaje incluye una porcion que tiene "lat:99.99999" y "long:78.26656" los cuales son los datos que permiten conocer la ubicacion del vehiculo, ademas de otro tipo de informacion. Que hice yo, cree un trigger para que con cada insert en la tabla inbox, seleccione ciertos campos y haga un substring para leer lat y long. Asi exportar a una tabla llamada Posiciones estos datos. Cuando llega el mensaje se ejecuta el trigger y copia los siguientes campos: Hora en que llego el mensaje(ReceivingDateTime), el numero que envio el mensaje (SenderNumber), Latitud (substring de Text) y Longitud (substring de Text).

Despues tengo una pantalla de seleccion, el cual solo muestra la Placa del vehiculo, ya que los demas datos son cofidenciales a la vista de un usuario comun. Al seleccionar la placa se hace una consulta a la BD y se busca un numero telefonico asignado a la placa seleccionada. Despues de esta consulta, realizo otra, la cual es seleccionar todas los registros de posiciones que tengan el numero de telefono obtenido de la consulta anterior con un limite de 1 registro ordenado de forma decreciente, esto fue loq mas se me ocurrio para realizarlo, y luego me abre el mapa con todos los datos.

Se que tengo que crear llaves foraneas e indexar las tablas para realizar las consultas sin perder velocidad a la hora de ejecutar las mismas, ahi radica el problema, no recuerdo como indexar la tabla y como hacer consultas mas eficientes para la bd. Espero que me logren ayudar. Adjuntare las tablas.


Tabla Inbox

Código:

ID_Inbox - int - autoincrement
UpdateInDb - timestamp - notnull
ReceivingDateTime - timestamp - notnull
SenderNumber - varchar(20) - notnull
Text - varchar(160) - notnull

Tabla Posiciones

Código:

ID_Posiciones - int - autoincrement
HoraRecibido - timestamp - notnull
nuTelefono - text - notnull
Latitud - text - notnull
Longitud - text - notnull

Tabla Usuarios

Código:

ID_Usuarios - int - autoincrement
admin - set('Si','No')
user - text - notnull
password - varchar(40) - notnull
Nombre - text - notnull
Apeliido - text - notnull
Cedula - int(8) - notnull
Telefono - text - notnull

Tabla Vehiculos

Código:

ID_Vehiculo - int - autoincrement
Placa - varchar(7) - notnull
Marca - varchar(20) - notnull
Modelo - varchar(20) - notnull
Ano - varchar(4) - notnull
nuAsignado - varchar(11) - notnull
FechaAgre - timestamp - current_timestamp

Espero que logren ayudarme con algo. Gracias!!

[guardarmicorreo]

hay algo importante que no dices de tu proyecto.

¿los sms a la base de datos solamente los puede enviar un usuario registrado o cualquiera?

te explico mi punto de vista.

si los sms solamente los puede enviar un usuario registrado en la bd, entonces está claro que debes relacionar la tabla inbox con usuarios, a su vez usuarios con vehículos y a su vez vehículos con posiciones.

si los sms los puede enviar cualquiera entonces inbox debe estar relacionada con vehículos y posiciones y usuarios ya no se para qué la querrías.

por otro lado, creo que debes discernir dos elementos en la BD:

una cosa son los datos que son esenciales en la BD y por otro lado los datos eventuales en la BD.

de manera que en la BD es esencial que haya un usuarios y por supuesto un vehículos, ambos relacionados (no puede haber usuario sin vehículos y viceversa...), pero eventualmente habrán posiciones y consultas (inbox).

por lo que, tanto la tabla posiciones como consultas(inbox) podrías reducirlo a una sola tabla. quitar el número telefónico registrado en la tabla usuarios y que en la nueva tabla unificada (posiciones+consultas) llamada eventos contenga todos los campos de posiciones y consultas(inbox) + el número desde el que se realizó la consulta.

ya que me imagino que un sistema bien pensado puede tener previsto que el usuario no siempre tenga que hacer la consulta desde el mismo número telefónico, sino que una vez identificado pueda realizar la consulta desde otro número telefónico.

sobre los índices te recomiendo este enlace, está todo muy bien explicado y detallado, además de la doc oficial de mysql claro está.

saludos :)