foreign key problems

Question

foreign key problems

Navigation

#1 by (0 votes)
#2 by (0 votes)

2

I am making a database that houses public transport data and I have a small lack of focus

this is my SQL:

CREATE TABLE IF NOT EXISTS 'prueba'.'lineas' (
 'idlineas' INT NOT NULL AUTO_INCREMENT,
 'numero' INT NOT NULL,
PRIMARY KEY ('idlineas'));

CREATE TABLE IF NOT EXISTS 'prueba'.'trayectos' (
'idtrayectos' INT NOT NULL AUTO_INCREMENT,
'ramal' VARCHAR(30) NOT NULL,
'itinerario' VARCHAR(500) NOT NULL,
PRIMARY KEY ('idtrayectos'));

CREATE TABLE IF NOT EXISTS 'prueba'.'tipo_buses' (
'idtipo_buses' INT NOT NULL AUTO_INCREMENT,
'descripcion' VARCHAR(15) NOT NULL,
PRIMARY KEY ('idtipo_buses'));

CREATE TABLE IF NOT EXISTS 'prueba'.'empresas' (
'idempresas' INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY ('idempresas'));

CREATE TABLE IF NOT EXISTS 'prueba'.'operador' (
'idoperador' INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY ('idoperador'));

CREATE TABLE IF NOT EXISTS 'prueba'.'lectores' (
'idlectores' INT NOT NULL AUTO_INCREMENT,
'serie' VARCHAR(20) NOT NULL,
PRIMARY KEY ('idlectores', 'serie'));

CREATE TABLE IF NOT EXISTS 'prueba'.'buses' (
'idbuses' INT NOT NULL AUTO_INCREMENT,
'idtipo_buses' INT NOT NULL,
'idlineas' INT NOT NULL,
'idtrayectos' INT NOT NULL,
'idempresas' INT NOT NULL,
'idoperador' INT NOT NULL,
'idlectores' INT NOT NULL,
'serie' VARCHAR(20) NOT NULL,
'chapa' VARCHAR(12) NOT NULL,
'coche_numero' INT NOT NULL,
'observaciones' VARCHAR(45) NULL,
PRIMARY KEY ('idbuses', 'idtipo_buses', 'idlineas', 'idtrayectos', 
'idempresas', 'idoperador', 'idlectores', 'serie'),
CONSTRAINT 'fk_tipo_buses_tipo_buses1'
  FOREIGN KEY ('idtipo_buses')
  REFERENCES 'prueba'.'tipo_buses' ('idtipo_buses'),
CONSTRAINT 'fk_lineas_lineas1'
  FOREIGN KEY ('idlineas')
  REFERENCES 'prueba'.'lineas' ('idlineas'),
CONSTRAINT 'fk_trayectos_trayectos1'
  FOREIGN KEY ('idtrayectos')
  REFERENCES 'prueba'.'trayectos' ('idtrayectos'),
CONSTRAINT 'fk_empresas_empresas1'
  FOREIGN KEY ('idempresas')
  REFERENCES 'prueba'.'empresas' ('idempresas'),
CONSTRAINT 'fk_lectores_lectores1'
  FOREIGN KEY ('idlectores' , 'serie')
  REFERENCES 'prueba'.'lectores' ('idlectores' , 'serie'));

As you can see I put as unique candidates in the table buses to sheet metal and car number so that they do not repeat, but when inserting the data I am not able to make both the idlectores or id operator do not repeat, that is, in the bus table can exist 2 buses that have the same operator to the same reader and that in practice should not be like that and I am not able to find where I can correct that, I think it is a problem of foreign keys, could you help me please

Update: Make the changes that advised me, so I leave the table but again I have the same problem 2 different buses that have the same operator and same reader ..

mysql sql

asked by Victor Gimenez 19.08.2018 в 18:59

source

2 answers

Doubt over hash, md5 and unencrypted Problems opening a file from PHP in a new window

score 0 · Answer 1

Table buses_has_trayectos has redundant columns. Let's see from top to bottom that table:

idtipo_buses, idlineas, idempresas, you do not need them there, they already exist in the buses table.
you must decide which column will link to lectores , if serie or idlectores . It does not make sense to put two columns of the same related table there.

The table may look like this:

`buses_has_trayectos`

idbuses

idtrayectos

idlectores

idoperador

If the table will handle a many to many relationship, it means that you will have n rows that expresses the number of relationships of that type. That is, if in the same path (path with id 4 to give an example) bus 1 has two operators (operator 2 and operator 7) and two readers (reader 1 and reader 8), that relationship would be expressed like this.

idbuses     idtrayectos     idlectores     idoperador
---------------------------------------------------------
1           4               1              2
1           4               8              7

If you want the data of operators and readers of a certain bus / path you just have to make a query joining the related tables and with a GROUP BY idbuses, idytrayecto .

If you want any of the columns that we got from the table because they were left over, you just have to take them from the tables they are in, because they share the JOIN that you are going to make.

NOTE: It does not mean that the many to many relationship example discussed above is the best one (it's just to explain how it works). This model would be optimal if a bus always has the same number of readers and operators on a trip. In a model where a bus can have for example 1 operator and 5 readers or vice versa, the relationship should be stated in another way. Why? Because a model where you will have rows or columns doing nothing , is a bad data model.

For example, imagine that on a bus / path there can be 6 readers and only 2 operators:

idbuses     idtrayectos     idlectores     idoperador
---------------------------------------------------------
1           4               1              2
1           4               8              7
1           4               9              NULL
1           4               10             NULL
1           4               11             NULL
1           4               12             NULL

You should rethink the data model, it is not good to have four columns doing nothing in the case of idoperador .

The way to solve this is multiple: from moving the relationship to another table related to journeys or groups or areas of operators / readers, to use columns of type ENUM to indicate who operates and who reads on each bus / Journey.

They are just ideas ... I can not tell you more without knowing in depth the data model and what the application does.

I hope it serves you.

score 0 · Answer 2

The explanation is very good, I really appreciate the time taking the last 2 paintings you did,

idbuses     idtrayectos     idlectores     idoperador
---------------------------------------------------------
1           4               1              2
1           4               8              7
1           4               9              NULL
1           4               10             NULL
1           4               11             NULL
1           4               12             NULL

and also an example of what happens:

idbuses     idtrayectos     idlectores     idoperador
---------------------------------------------------------
1           4               1              7
2           4               1              7

There are 2 buses sharing the same reader and operator, this is what I want to avoid. For this I saw in the need to put as unique fk idlectores and idoperador in that way I avoid that they repeat as I showed above, this is the solution that I gave, I do not know if the most appropriate to the case but is the only one it occurred to me.