Handling Many to many relationship

Question

Here what table im working on:
restaurant (restaurant information)
restaurant_hours (restaurant operational hours)
restaurant_tags (tag about restaurant)

1 restaurant can have more than 1 restaurant_hours and more than 1 restaurant_tags

Result of querying multiple table:

+----+---------------+-------+---------+---------+-----------+------------+-------+
| id | name          | state | cuisine | day     | open_time | close_time | tag   |
+----+---------------+-------+---------+---------+-----------+------------+-------+
| 44 | Restaurant A  | png   | local   | mon-sun |   08:00   | 19:00      | none  |
| 45 | Restaurant B  | kdh   | western | mon-sun |   00:00   | 19:00      | none  |
| 46 | Restaurant C  | png   | western | mon-fri |   09:00   | 19:00      | none  |
| 46 | Restaurant C  | png   | western | sat-sun |   10:00   | 23:00      | wifi  |
| 46 | Restaurant C  | png   | western | sat-sun |   10:00   | 23:00      | coke  |
| 46 | Restaurant C  | png   | western | sat-sun |   10:00   | 23:00      | cofee |
+----+---------------+-------+---------+---------+-----------+------------+-------+

As you can see restaurant with id 46 has multiple row due to multiple hours of operation (day, open_time, close_time) value or tag value.

This data quite cumbersome to work with, i cant assume each row belong to different restaurant

How can i make application code easier to process this data?

One way I can think of is merge restaurant into single row (Is this the right way? Is there any other way?)

Using subquery and group_concat, I end up with something like this:

+---------------+---------------------+-------+---------+-------------------------------------+-------------------------+----------------------------+-------------------------+
| restaurant_id | name                | state | cuisine | day                                 | open_time               | close_time                 | tag                     |
+---------------+---------------------+-------+---------+-------------------------------------+-------------------------+----------------------------+-------------------------+
|            44 | Restaurant A        | png   | local   | mon-sun                             | 08:00                   | 19:00                      | none                    |
|            45 | Restaurant B        | kdh   | western | mon-sun                             | 00:00                   | 19:00                      | none                    |
|            46 | Restaurant C        | png   | western | mon-fri , sat-sun, sat-sun, sat-sun | 09:00,10:00,10:00,10:00 | 19:00, 23:00, 23:00, 23:00 | none, wifi, coke, cofee |
+---------------+---------------------+-------+---------+-------------------------------------+-------------------------+----------------------------+-------------------------+

Generally this is what i have been doing for some time.
Application code just need to split by comma for any column that might have more than 1 value

Question:
1)Is this the right way to do it?
2)Is there a limit to column length, cause group_concat might produce very wide column

I would love to know how other programmer handle this kind of problem

Edited
Using php as an application code

Answer 1

Group_concat might not be your best option, as there is a size limit (and it's not a hard limit, it's based on a few other settings). Quoting from the mysql 5.0 docs

The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet.

Just don't try a "one size fits all" query for everything you're doing. I'd not join to all the tables every time (if you don't have to), and only do that when needed. If you need to query this information most of the time, it may be easier to have a "post processing" function on your app side that converts this to an easier form-- like say, a multi dimensional array. Now, the fact that you have 2 1-many relationships going on, it compounds the problem. It may be easier to simply run nested queries, depending on how much data we're talking about-- IE, get all the restaurant data, then for each restaurant, get the hours, then get the tags. If this induces hundreds of queries, you can eliminate all of that by querying the restaurant table and then querying every row of the hours and tags tables, performing the "join" in your procedural code. The trick here is to do it in a way that can be used throughout your app without slowing things down or using too much memory. If we're talking about less than few hundred restaurants and we don't have blob columns in there very large text columns, you should be good memory wise to load it all up and process in php.. otherwise, you may just have to process this as you go, which, as you're discovering is ugly.

The absolute best case is that you won't have to pool all of this data together most of the time.

Answer 2

Multi value fields are always a pain to work with. Though the first method yields more columns it is also more concise and easy to query.

Short answer: If you value space choose option 2. If you value usability choose option 1.