Insert multi information for smae id

Question

How can i insert in MYSQL for one id multi - inputs as shown below : without repeating "vehicle code" as i activate auto increment

Thanks Regards

Answer 1

You have two entities: vehicle and tyre. They have a many-to-many relationship to each other

So you need three tables.

vehicle:

vehicle_id(PK)   vehicle_no  vehicle_code
1                AST-001     V-01
2                BTU-001     Q-99

tyre:

tyre_id(PK)  size   pattern (size and pattern are part of a UNIQUE key)
1            AB     BB
2            AC     CC
3            AD     XX
4            AE     YY
5            AF     ZZ
6            AG     AA
7            PA     R1

vehicle_tyre:  (to handle the many-to-many relationship)

vehicle_id  tyre_id  tyre_no   (all columns are part of a composite primary key)
1           1         1
1           2         2
1           3         3
1           4         4
1           5         5
1           6         6
2           1         7    (this vehicle has four tyres, all the same)
2           2         7
2           3         7
2           4         7

When your app has a new type of tyre to insert you do this. The IGNORE together with the UNIQUE index on (size, pattern) prevents the insertion of duplicate tyres without lots of extra work.

INSERT IGNORE INTO tyre (size, pattern) VALUES (?, ?);  #[size, pattern]

When you hae a new vehicle to insert you do this. You run the following three SQL statements, and repeat the last one for each tyre on the vehicle.

INSERT INTO vehicle (vehicle_no, vehicle_code) VALUES (?,?);  #[no, code]
SET @vehicle_id := LAST_INSERT_ID();
INSERT INTO vehicle_tyre (vehicle_id, tyre_id, tyre_no)
             SELECT       @vehicle_id, tyre_id, ?
               FROM tyre
              WHERE size = ? AND pattern = ?;   #[tyre_no, size, pattern]

LAST_INSERT_ID() is the trick to getting the vehicle_id right in the vehicle_tyre table. Notice this: after you do an INSERT with python, you can get the LAST_INSERT_ID() value using cursor.lastrowid or connection.insert_id(). See this.