Database structure with OneToOne annotations

Question

I'm writing a webapp and can't figure out which one of the two following ways should I take. Imagine 5 tables: A, B, C, D, and E.

Option 1: Use ids as foreign keys and then it looks like this:
tableA: id, field1, field2, ...
tableB: tableA_id, field1, field2, ...
tableC: tableB_id, field1, field2, ... etc...

Option 2: Create new columns that will serve as foreign keys and then it looks like this:
tableA: id, field1, field2, ...
tableB: id, tableA_id, field1, field2, ...
tableC: id, tableB_id, field1, field2, ... etc...

I think option 1 might be a bit confusing when we call tableB id field which is infact named tableA_id but I might be wrong so I'll be glad if someone enlightens me a little.
Also, if option 2 is the way to go, what is the point of joining tables on primary key?

EDIT: What I am trying to achieve is something like this:
Account
...Character
......Items
......Messages
......Other stuff

I don't think so. I'm just trying to figure out which way is the proper way. In my case there is account table which holds exactly 1 character which holds for example items table, messages, etc etc — Sikor

mainstreetmark mainstreetmark · Accepted Answer · 2014-10-30T22:32:42

I guess "Option 2", though you may need to be a bit more specific as to your goal here. Might I suggest an Option 3:

 TableX: id, A_id, B_id, C_id, D_id, E_id

then:

Table_A: id, col1, col2
Table_B: id, colX, colY
Table_C: id, thing1
Table_D: id, more_col
Table_E: id, xxx

Then, it's just a simple join:

 SELECT A.col1, B.colX, C.thing1, D.more_col, E.xxx
    FROM TableX X
    LEFT JOIN Table_A as A on X.A_id = A.id
    LEFT JOIN Table_B as B on X.B_id = B.id
    .
    .
    .

Database structure with OneToOne annotations

2 Answers