What's the easiest method to place rows into tables with references 1-to-1 of one another?
I am talking about, inside a MySQL 5.5 and tables InnoDB, I've got a database design like the following
The issue arises whenever we attempt to place rows in table1 and table2. Since there's no multi-table place in MySQL, I am unable to place a row becouse the foreign secrets aren't NULL fields both in tables and really should be placed concurrently both in.
The bes method to solve this issue?
I are thinking about 3 possible solutions, but I wish to determine if you will find a lot more than these or the best idea and why.
Set the foreign key area as NULLABLE and after place one row inside a table, place another one and later on, update p first one.
Just like indicated above however with an special value like -1. First, place in a single table with foreign
key = -1that's equal to NULL but staying away from set the area as NULLABLE. Later on, we place the row within the other table increase the first placed.
Produce a relational table between both though it's not really necessary since it is a 1-to-1 ratio
EDIT I briefly explain things i need this circular relationship: It's a denormalization in the parent table to 1 of their children's. It's produced in order of high end to possess always the reference of the greatest rated child from the parent table.
I'll get this to a solution when i feel this can be a design flaw.
First, when the two tables have been in true
1:1 relationship, why not simply have one table?
Second, when not a genuine
1:1 relationship but a supertype-subtype problem, you do not need this circular foreign secrets either. Allows say
Customer. Off beam most clients aren't employees (and vice-versa). But a customer might be an worker too. This is often solved getting 3 tables:
Person ------ id PRIMARY KEY: id Employee -------- personid lastname firstname ... other data PRIMARY KEY: personid FOREIGN KEY: personid REFERENCES Person(id) Customer -------- personid creditCardNumber ... other data PRIMARY KEY: personid FOREIGN KEY: personid REFERENCES Person(id)
Within the scenario you describe you've two tables
1:N relationship. Then, you need to store in some way the very best carrying out (with different defined calculation) child for each parent.
Would the work?:
Parent ------ id PRIMARY KEY: id Child ----- id parentid ... other data PRIMARY KEY: id FOREIGN KEY: parentid REFERENCES Parent(id) BestChild --------- parentid childid ... other data PRIMARY KEY: parentid FOREIGN KEY: (childid, parentid) REFERENCES Child(id, parentid)
By doing this, you enforce the wanted referential integrity (every BestChild is really a Child, all parents only has one BestChild) and there's no circular path within the References. The mention of best child is saved within the extra table and never within the
You'll find BestChild for each Parent by joining:
Parent JOIN BestChild ON Parent.id = BestChild.parentid JOIN Child ON BestChild.childid = Child.id
Furthermore, if you wish to store best children for multiple performance tests (for various kinds of tests, or tests in a variety of dates), you can include a
test area, and affect the Primary Answer to
BestChild --------- testid parentid childid ... other data PRIMARY KEY: (testid, parentid) FOREIGN KEY: (childid, parentid) REFERENCES Child(id, parentid) FOREIGN KEY: testid REFERENCES Test(id)