Hi As you can see there are 2 kinda designs we've got here. Both of them are based on that there is only one row per main table or mains table row in the other relation tables. [1:1]
Design 1: there are null-able foreign-keys of tables; items, others, seconds in mains table
Design 2: there is foreign-key of main table in others
My Question is, which design is appropriate? For making Select SQL query, in "Design 1" there is a need of using UNION and for "Design 2" LEFT JOIN would be the choice I assume. So, in terms of performance through large amount of data which is suitable and how should be the sql query?
SELECT mains.id as mainId, items.id as id, mains.title as title, items.name as name
FROM items, mains where mains.itemId=items.id
UNION
SELECT mains.id as mainId, others.id as id, mains.title as title, others.name as name
FROM others, mains where mains.otherId=others.id
UNION
SELECT mains.id as mainId, seconds.id as id, mains.title as title, seconds.name as name
FROM seconds, mains where mains.secondId=seconds.id
------------------------------
SELECT *
FROM mains
LEFT JOIN items ON mains.itemId=items.Id
LEFT JOIN seconds ON mains.secondId=seconds.id
LEFT JOIN others ON mains.otherId=others.id
------------------------------
SELECT main.id as mainId, item.id as id, main.title as title, item.name as name
FROM item, main where main.id=item.mainId
UNION
SELECT main.id as mainId, other.id as id, main.title as title, other.name as name
FROM other, main where main.id=other.mainId
UNION
SELECT main.id as mainId, second.id as id, main.title as title, second.name as name
FROM second, main where main.id=second.mainId
------------------------------
SELECT *
FROM main mains
LEFT JOIN item items ON mainmains.id=itemitemId=items.mainIdId
LEFT JOIN second seconds ON mainmains.id=secondsecondId=seconds.mainIdid
LEFT JOIN other others ON mainmains.id=otherotherId=others.mainIdid different sql query approach..! which one? performance?
P.S. needless to say others, seconds, other, second tables, each of them has like 10 relation tables which I omited in here..
