One to one relationship involving multiple tables

Question

Say,

I have 3 tables.

User which contains basic info about the users.

SectionA which contains more info about the user.

SectionB which also contains more info about the user.

There can only be one SectionA and SectionB data for each user.

My idea was to create a table design like this:

id  name   section_a_id   section_b_id
1   matt   1               1

Problem is, section_a_id and section_b_id cannot be auto incremented since they are not primary keys.

So I tried a different approach and decided that the id primary key in User should be a foreign key that refers to section_a_id and section_b_id`. But I'm unable to do so since mysql will only allow a reference to one table.

So how should I approach this situation?

Answer 1

If it's one to one relation, it will always be easier to combine the three tables into one big table, with nullable columns for Section tables.

Some positive points I can see for this approach:

• Easier insert, update and delete operations.
• Faster data retrieval when there are less joins to use.
• Less indexes space, because you are indexing the primary key for one table instead of three tables.

Answer 2

It seems you have:

• 1-n User-SectionA relation
• 1-n User-SectionB relation

so the foreign keys have to be in SectionA and SectionB tables and they have to be the User table primary key.