design issues as to circular foreign keys

Question

I'm in the final stages of my designing my database and have the following design issue.

I have two tables, A and B. These tables are essentially in a one-to-one relationship - it doesn't make sense for an A-row to exist without a corresponding B-row and vice versa.

My initial thoughts were to create mutual foreign keys but will this create issues if I delete one (chicken and egg problem)? I'm using mysql.

I've heard people say 'if they're in a one to one relationship just put them in one table'. However, I have the feeling (and it is just a feeling) that they should be in seperate tables because they are distinct entities.

Should I put them in one table? Are there issues with circular foreign keys?

Thanks

Answer 1

Take a look at the questions and the info under these three tags:

single-table-inheritance class-table-inheritance shared-primary-key

The technique known as shared primary key is useful for modeling IS-A relationships and other one-to-one relationships. The use of a foreign key as a shared primary key enforces the one-to-one nature of the relationship. Since one copy is a copy of the other, you won't be in the circular bind that occasioned this question.

The relationship between conversions and tries is really zero-or-one-to-one. Not all tries have a conversion. This means that the conversion should have a foreign key to the try. If there's no conversion, there's no row. End of discussion.

If, for some strange reason, you really need standard tries and penalty tries in separate tables, you can use Class Table Inheritance in conjunction with shared primary key to model the IS-A relationships between standard tries, penalty tries, and tries.

I would just put tries in one table.

Answer 2

In rugby you can't have more than one conversion per try (or penalty try), therefore you can't have multiple B's for one A (as you stated in one of your comments). There is usually one conversion attempt for each try - it's a 1-1 relationship. Also, a try will always exist before its conversion.

Are you also storing penalty conversions in the conversion table? If you are then these penalty conversions will not be associated with any try, and so there is not a hard 1-1 relationship with tries, and a foreign key to the try table must accept nulls. Similarly, because you would create an entry in the try table before its associated conversion, a foreign key to conversions must also accept nulls.

To me the easiest way would be to create a foreign key in the conversion table back to the associated try. If penalty conversions are also kept in the same table that foreign key would accept nulls. This way you can insert the try first (assuming that you must do so temporally) without worrying about referential integrity to the conversion table. Then you can add the conversion later without having to go back and update the try table's reference to newly added conversion.

Answer 3

I am hard-pressed to think of examples of 1-1 relationships between distinct entities. Usually, there is an option on one side or the other. Consider books resting on shelves. Each slot on a shelf has one book. And a given book occupies one slot. But you could have empty slots, unshelved books, or books could switch slots over time. This is just an example.

The next question is access paths. If both "entities" are accessed at the same time, then consider putting them into a single table. I say this with a caveat. Sometimes you do want to split columns into separate tables (often for performance reasons). This is called vertical partitioning.

If they really are separate entities, then use two tables, each with its own primary key. Include a foreign key that refers back to the other table. Be careful when inserting records to maintain the referential integrity.

If they are the same entity but with different columns, then use the same primary key for both. Have the key be auto-incrementing in one table. Then insert the value into the second table when you create that record.