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 enter image description here

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.

  1. 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.

  2. Just like indicated above however with an special value like -1. First, place in a single table with foreign key = -1 that'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.

  3. Produce a relational table between both though it's not really necessary since it is a 1-to-1 ratio

Thanks!!

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 table1 is Employee and table2 is 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 Parent and Child getting 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 Parent table.

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 (test, parentid):

BestChild
---------
testid
parentid
childid
... other data
PRIMARY KEY: (testid, parentid)
FOREIGN KEY: (childid, parentid)
    REFERENCES Child(id, parentid)
FOREIGN KEY: testid
    REFERENCES Test(id)