0

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?

1

No, its not necessary. While it might be convenient for browsing through the data manually, it doesn't impact the program at all since you already have the textid in the second column. Don't worry about what the auto_increment key value is in the tables. Just look at it as a guaranteed unique value that can be use to identify the row.

| improve this answer | |
  • do don't u worry about run out of number of auto-increse key in table2? – Tum May 31 '14 at 7:43
  • 2
    You are going to run into much tougher scalability issues long before you max out int32. Running out of auto-increments is rare, easy to solve, and the last thing you should worry about. – Vatev May 31 '14 at 8:25
  • really? does BigInt type in Mysql make the app run slower? – Tum May 31 '14 at 9:25
  • No, not in any way you'd notice. – GrandmasterB May 31 '14 at 22:35
0

The unneccesary complexity you are facing originates from the fact that your design is not normalized.

If two entities have a one-to-one relationship, you have to reconsider whether they should be a single entity.

Unless Table2 represents a role specialization of Table1, which it doesn't seem to be since Table2 has no other columns, there's no need of them being separate tables.

In a normalized design, that complicated update strategies never arise. Normalization eliminate update inconsistencies.

| improve this answer | |
  • not 100% 1 - 1, cos 1 textID can have a few rID – Tum May 31 '14 at 3:55
  • besides table2 is a table that connect table1 with other table. Table2 is like a middle table. – Tum May 31 '14 at 3:56
  • @tum Please update your question, and don't ommit relevant information like Table2 being a many-to-many between two tables, which is crucial to understanding the problem. – Tulains Córdova May 31 '14 at 3:58
  • done. I just modified – Tum May 31 '14 at 4:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.