K
K
kefiijrw2011-01-06 01:20:31
Database design
kefiijrw, 2011-01-06 01:20:31

Implementing changing a record in a relationship table with a composite primary key?

I ran into a seemingly standard situation, but I can’t find a non-crutch solution (or maybe I’m just so gentle). The feeling that everything is very simple and elementary is solved, and I am a narrow-minded orangutan who knows nothing.
There are two tables A and B, the primary keys id are standard surrogate ones, these same INT NOT NULL AUTO_INCREMENT PRIMA ...
To implement a many-to-many relationship between them, there is a relationship table A_B, consisting of two fields: a_id and b_id - foreign keys of tables A and B respectively. The primary key, which seems logical, consists of this pair of foreign keys.
There is a phpbydlocoder database management admin panel. Specifically, a page for editing links between A and B. More specifically, a form for editing all records of table A_B with some specific a_id. The result is about 20 entries per page, everything is edited through inputs with a common submission.
PROBLEM: the php script that processes the form on submission receives the changed links and does not know what they were before editing, because the keys by which they were identified have changed. That is, if the user changes | | a_id | b1_id | on | a_id | b2_id |, then the script knows what to change to, but does not know what it was originally | a_id | b1_id |.
I thought hard, found some solutions, but they do not suit me:
1) store in the form hidden-fields with the initial values ​​of the links, send them to the submitter and use them to determine the database record being changed. Dislikes: crutch feeling; security issues, although they are, generally speaking, of the least concern.
2) add one more field to the link table - a surrogate immutable id, by which you can identify the changed link. As in the previous paragraph, store it in a hidden form field. Dislikes: see previous point + wrath of the gods of relational data models; the need to paw the structure of the database for the sake of such a trifle.
3) change = removal + addition. Clean up all 20 entries and add again. Dislikes: crutch feeling; deleting and adding many records for the sake of changing the only one (this is 20 in this case, in the other there will be all 120)
enlighten me

Answer the question

In order to leave comments, you need to log in

3 answer(s)
N
niko83, 2011-01-06
@kefiijrw

before saving, we get from the database what is by the a_id key and
compare it with what came from the user. Using array_diff () and other tricks, we find those lines that need to be removed and those that need to be added. If their number is the same (if the form implies update functions, then it will be so), then you can replace one request with update.
The logic becomes more complicated, but there is no mass deletion as you described in paragraph 3)

M
Monca, 2011-01-06
@Monca

The second and third options are quite vital.
Did not write what kind of database. The simplest is to put a cascading change (if any). Or make a cascade through triggers (if any).
But if with little blood, then the third option is not bad, you can safely use it.
The first option doesn't work at all.

M
mmib, 2011-01-06
@mmib

and why the anger at option 2) if it suggests itself by setting the task. Yes, it will no longer be the 3rd normal form, but it will not make you worse. And it will be very convenient to update such lines. The main thing is not to forget to make the pair a_id, b_id unique
1) a completely crutch option
3) if a lot of data will work slowly,
data redundancy in option 2) is most likely the least of three evils

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question