References Tables: all in one or separate table?

I have a group table - should option_id or extras_id should be in separate tables 开发者_C百科or all in one table? See below what I meant:

group table:

mysql> select * from `group`;
+----+-----------+--------+
| id | name      | type   |
+----+-----------+--------+
|  1 | Group One | extra  |
|  2 | Group Two | option |
+----+-----------+--------+

There are two of group (extra or option).

group_extra table:

mysql> select * from `group_extra`;
+----+----------+----------+
| id | group_id | extra_id |
+----+----------+----------+
|  1 |        1 |      123 |
|  2 |        1 |      124 |
+----+----------+----------+

group_id = 1 have a list of ref extra_id

group_option table:

mysql> select * from `group_option`;
+----+----------+-----------+
| id | group_id | option_id |
+----+----------+-----------+
|  1 |        2 |        45 |
|  2 |        2 |        46 |
+----+----------+-----------+

group_id = 2 have a list of ref option_id

group_option_extra table:

mysql> select * from `group_option_extra`;
+----+----------+-----------+----------+
| id | group_id | option_id | extra_id |
+----+----------+-----------+----------+
|  1 |        1 |         0 |      123 |
|  2 |        1 |         0 |      124 |
|  3 |        2 |        45 |        0 |
|  4 |        2 |        46 |        0 |
+----+----------+-----------+----------+

Or should the table look like this, combine group_option and group_extra into one? which one is recommended.

---

Keep them separate.

You don't really need the type column - you can just check if any rows exist in the other tables to see which type it is.

---

Separate.

"together" is known as the "One True Lookup Table" anti-pattern

See

• sql performance of a lookup table
• http://tonyandrews.blogspot.com/2004/10/otlt-and-eav-two-big-design-mistakes.html