database design best practice connecting multiple tables

Question

sorry if this question has been asked heaps before, but I didn't know how to word it in a way that google would be able to understand.

Basically if for example you have 3 tables:

Table A

Table B1

Table B2

and the data from all 3 tables are connected in 1 of 2 ways either:

Table A & Table B1

OR

Table A & Table B2

Which would be best practice to connect them in a table and why?

1 table such as:

Joined table

    |Table A  |Table B1  |Table B2  |
    |tableA_ID|tableB1_ID|null      |
    |tableA_ID|null      |tableB2_ID|

or have 2 seperate tables for each join

Table A and B1 joined

Table A and B2 joined

Or is there another better way?

http://stackoverflow.com/questions/13749525/relational-database-design-multiple-user-types/13752304#13752304 — Walter Mitty, Sep 07 '16 at 18:52
Thanks Walter, after some playing with it yesterday I had kind of come to the same conclusion, this helps clarify my thoughts on it. how can I accept this as the answer...? — Suxorixorage, Sep 08 '16 at 04:26
It's already accepted as the correct answer over in its original context. I decided against posting a redundant answer here. — Walter Mitty, Sep 08 '16 at 11:08

score 1 · Answer 1 · answered Sep 07 '16 at 01:50

1

Joining table depends upon the Fields and Relationship among the tables. It also depends on the output you are looking - based on this you will need to join the tables

answered Sep 07 '16 at 01:50

Senthil_Arun

1,008
9
15

score 0 · Answer 2 · answered Sep 07 '16 at 01:49

0

I think you want a left join, two in fact:

select a.tableA_ID, b1.tableB1_ID, b2.tableB2_ID
from a left join
     b1
     on a.tableA_ID = b1.tableA_ID left join
     b2
     on a.tableA_ID = b2.tableA_ID ;

answered Sep 07 '16 at 01:49

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 3 · answered Sep 07 '16 at 01:55

It's a bit unclear what you're trying to do, but given your expected results, union all might work:

select a.tableA_ID, 
       b.tableB1_ID as TableB1, 
       null as TableB2
from a join b on a.tableA_ID = b.tableA_ID
union all
select a.tableA_ID, 
       null,
       b2.tableB2_ID
from a join b2 on a.tableA_ID = b2.tableA_ID

database design best practice connecting multiple tables

3 Answers3