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 ...
textIDis auto_increment primary key
rIDis auto_increment primary key &
textIDis foreign key
The relationship is that 1
rIDwill have 1 and only 1
textIDcan have a few
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?