php - Conditional Join Statement in MySQL using IF-ELSE -

i'm making notification scheme social networking app. i've different kind of notification categorized in 2 groups: friends-related , events-related. currently, database schema this:

+---------------------+------------------------+------+-----+---------+----------------+ | field               | type                   | null | key | default |          | +---------------------+------------------------+------+-----+---------+----------------+ | notification_id     | int(11)                | no   | pri | null    | auto_increment | | notification_type   | enum('event','friend') | no   |     | null    |                | | notification_date   | datetime               | no   |     | null    |                | | notification_viewed | bit(1)                 | no   |     | null    |                | | user_id             | int(11)                | no   | mul | null    |                | +---------------------+------------------------+------+-----+---------+----------------+ 

now, i've 2 different tables fro event-related notification , friend-related notification. below schema event-related notification table:

+-------------------------+----------------------------------------------------+------+-----+---------+-------+ | field                   | type                                               | null | key | default | | +-------------------------+----------------------------------------------------+------+-----+---------+-------+ | notification_id         | int(11)                                            | no   | pri | null    |       | | event_id                | int(11)                                            | no   | mul | null    |       | | event_notification_type | enum('added','kicked','new-message','info-edited') | no   |     | null    |       | +-------------------------+----------------------------------------------------+------+-----+---------+-------+ 

and again i've 4 more tables each kicked, added, new-message, info-edited type of notification, since each requires have different kind of property (for example kicked requires reason).

now, want write conditional sql query such joins notification event_notification if notification_type event otherwise different.

select * notification_table t t.seen = false , t.user_id = ? inner join event_notification en on(t.notification_type='event' , en.notification_id = t.notification_id) inner join .....

there going many inner joins there better way of doing it? think query not optimized either, appreciate if provided.

you can use joins. however, want create query using left outer joins rather inner joins:

select * notification_table t t.seen = false , t.user_id = ? left join       event_notification en       on(t.notification_type='event' , en.notification_id = t.notification_id) left join ... 

don't worry proliferation of joins. if tables have proper indexing, perform fine.

do consider changing data structure have 1 table different notification types. having few fields not used not add performance overhead, when consider complications of having many joins , additional management overhead of having more tables.