The implementation of the entry change in the table context with a composite primary key?

0 like 0 dislike
Faced with a seemingly standard problem, but I can't find neostyle solution (or maybe I'm just a sweet). The feeling that everything is very simple and elementary solved, and I'm not ashamed who knows nothing.

There are two tables A and B primary keys are id — surrogate standard these INT NOT NULL AUTO_INCREMENT PRIMA...

For the realization of communication "many-to-many" has a table of context, A_B, consisting of two fields: a_id and b_id — foreign key tables A and B, respectively. The primary key that seems logical, consists of this pair of foreign keys.

There are phpmygallery admin database management. In particular, the page for editing links between A and B. more specifically, the edit form all records in the table A_B with some specific a_id. The result is approximately 20 rows per page, all editable via input General submit.

PROBLEM: php script that handles the form on submit gets changed when and don't know what they were before editing, because the keys in which they are identified have changed. That is, if the user changes | a_id | b1_id | | a_id | b2_id |, the script knows what to change, but doesn't know what it was originally | a_id | b1_id |.

I started thinking to myself, found some solutions, but they do not suit me:

1) store in a hidden form field with the original values links, send them by submit and for them to determine variable entry database. Didn't like the feel of the crutch; security issues, although they generally care about the least.

2) to add to the table when another field — surrogate unchanging id, which identifies a modified contact. As in the previous paragraph, storing it in a hidden-form field. Don't like: see the previous item + the wrath of the gods relational data models; the need lapetite the structure of the database for such things.

3) change = remove + add. To clean up all 20 records and add new. Didn't like the feel of the crutch; deleting and adding multiple entries for a single change (in this case 20, in another are all 120)

talk some sense into me
by | 5 views

3 Answers

0 like 0 dislike
before saving get from the database what is the key a_id, and
compare with what came from the user. Using array_diff() and other trick find the rows you want to delete and those you want to add. If their number the same (if the form implies update functions then so be it), it is possible to substitute one request for update.
Complicated logic, but there is no mass removal such as you describe in paragraph 3)
0 like 0 dislike
The second and third options it is vital.
Did not write that database. The easiest thing is to put a cascading change (if any). Or to make a cascade using triggers (if any).
But if a little blood, then the third option is not bad, you can safely use.
The first option is not good.
0 like 0 dislike
and why the anger on option 2) if he suggests a formulation of the problem. Yes, it will not be 3rd normal form, but it will not be worse. And update these lines will be very convenient. Don't forget to make a couple a_id, b_id unique
1) does spike option
3) if a lot of data will be slow to work
data redundancy in option 2) is likely the least of three evils
110,608 questions
257,186 answers
28,758 users