This question is about good design practice in programming.
Let see this example, we have 2 interrelated tables:
Table1
textID - text
1 - love..
2 - men...
...
Table2
rID - textID
1 - 1
2 - 2
...
Note:
In Table1:
textID is auto_increment primary key
In Table2:
rID is auto_increment primary key & textID is foreign key
The relationship is that 1 rID will have 1 and only 1 textID but 1 textID can have a few rID.
So, when table1 got modification then table2 should be updated accordingly.
Ok, here is a fictitious example. You build a very complicated system. When you modify 1 record in table1, you need to keep track of the related record in table2. To keep track, you can do like this:
Option 1: When you modify a record in table1, you will try to modify a related record in table 2. This could be quite hard in term of programming expecially for a very very complicated system.
Option 2: instead of modifying a related record in table2, you decided to delete old record in table 2 & insert new one. This is easier for you to program.
For example, suppose you are using option2, then when you modify record 1,2,3,....,100 in table1, the table2 will look like this:
Table2
rID - textID
101 - 1
102 - 2
...
200 - 100
This means the Max of auto_increment IDs in table1 is still the same (100) but the Max of auto_increment IDs in table2 already reached 200.
what if the user modify many times? if they do then the table2 may run out of records? we can use BigInt but that make the app run slower?
Note: If you spend time to program to modify records in table2 when table1 got modified then it will be very hard & thus it will be error prone. But if you just clear the old record & insert new records into table2 then it is much easy to program & thus your program is simpler & less error prone.
So, is it good practice to keep 2 related tables (using auto_increment PK) to have the same Max of auto_increment ID when table1 got modified?
