Improved Approach for Updating Multiple Data

Question

I have this MySQL table where the contact_id string is unique to each user_id .

history:
- hist_id: int(11) auto_increment primary key
- user_id: int(11)
- contact_id: int(11)
- name: varchar(50)
- phone: varchar(30)

From time to time, the server receives a new contact list for a specific user_id and must update this table, insert, delete or update data that differs from previous information.

For example, current data:

So, the server receives this data:

And new data:

As you can see, the first row (John) has been updated, the second row (Mary) has been deleted, and the other row (Jeniffer) has been included.

Today, what I am doing is deleting all rows with a specific user_id and inserting new data. But the auto-increment field (hist_id) is getting bigger and bigger ...

Obs: 80 , 30 .

() :

1. , , user_id ?

2. ? , , .

3. , , - , user_id/contact_id ?

PS.

!

Answer 1

1.    , user_id ?

. "upsert", " ". , , , , . . , . , user_id contact_id.

INSERT INTO history (user_id, contact_id, name, phone)
VALUES
    (1, 23, 'James Jr.', '(619)-543-6222')
ON DUPLICATE KEY UPDATE
    name=VALUES(name),
    phone=VALUES(phone);

contact_id, .

2. ? , , .

. varchar , . ? . , , , . , . , . , ? , "--". , , . , , -. .

3. , , , , user_id/contact_id ?

, . SQL, . , , - , . , , , angular.

, , , . , user_id - , . , , . , , .

: -, , . , , , . , MySQL Workbench.

_{(: xonos.net)}

SQL, .

, "person" , . "" , "" "" . , ? .

1.) , -, " ". " " , . " " , . , , . "" Jim Bean, , .

2.) , , . - . , , Person. . / "CASCADE" - .

. , , .