Data insertion with junction tables

问题:

I'm working on my first major database design with junction tables in CodeIgniter, and I'm not too sure how to handle data insertion.

Let's say I have two tables, some_table & another_table, with a junction table, some_another. Now if I want to add a record to some_table, do I also write separate insertion queries for the junction table, or is there a better, more automated way to handle this?

For example, do I have to do this:

INSERT INTO some_table (col1, col2, col3) VALUES (val1, val2, val3)

INSERT INTO some_another (col01, col02) VALUES (val01, val02)

for each record, or is there a way or a tool that handles also updating junction tables for me?

If there's any other info I should provide, please let me know. Thanks!

回答1:

Depends on the purpose of the "junction table." If you just want a join of all records between the other two tables, use a join for that. If you want a "many to many" correlation, there's really not an automated way of knowing which ones should be associated.

If, however, you want to simulate a join and simply "preload" all the relationships into a many-to-many table (useful if you want default relationships that could be disabled or manipulated later), you could use triggers for that.

Regardless, you'll need to use separate queries for every table, as MySQL can't insert into multiple tables with a single query.