mysql: using comma-separated values

Question

I have a simple mysql table which I use as a server booking system. A user can log in to a server, if he has a valid booking for that server. A sample data might look like this:

+----------+----------------+---------------------+---------------------+
| server   | user           | start               | end                 |
+----------+----------------+---------------------+---------------------+
| server01 | Alice,Bob      | 2013-10-28 00:00:00 | 2013-12-31 00:00:00 |
| server02 | Carlos         | 2013-10-28 00:00:00 | 2013-12-31 00:00:00 |
| server03 | Dan,Erin,Frank | 2013-10-28 00:00:00 | 2013-12-31 00:00:00 |
+----------+----------------+---------------------+---------------------+

To check whether user Carlos has a booking for server02, I use the following SQL query:

SELECT 0, id FROM booking WHERE user = 'Carlos'
AND (( start < now() and end > now() ) AND server = 'server02') ;

But this query does not work if there are multiple (comma-separated) users. How can I check if Alice has booking for server01 ?

Answer 1

While your booking table is in very serious (critical, severe, dire, vital, etc,) need of normalization on the user column, here is what you can do in the meantime...

SELECT 0, id FROM booking
WHERE ((start < now() and end > now())
AND server = 'server02') AND
LOCATE(',Alice,',CONCAT(',',user,',')) > 0;

Give it a Try !!!

CAVEAT : Please normalize your data as Johnny000 just suggested !!! Why???

You will not get any decent query performance searching by user in CSV format.

If you normalize user, your data should like this:

+----------+--------+---------------------+---------------------+
| server   | user   | start               | end                 |
+----------+--------+---------------------+---------------------+
| server01 | Alice  | 2013-10-28 00:00:00 | 2013-12-31 00:00:00 |
| server01 | Bob    | 2013-10-28 00:00:00 | 2013-12-31 00:00:00 |
| server02 | Carlos | 2013-10-28 00:00:00 | 2013-12-31 00:00:00 |
| server03 | Dan    | 2013-10-28 00:00:00 | 2013-12-31 00:00:00 |
| server03 | Erin   | 2013-10-28 00:00:00 | 2013-12-31 00:00:00 |
| server03 | Frank  | 2013-10-28 00:00:00 | 2013-12-31 00:00:00 |
+----------+--------+---------------------+---------------------+

If you do the reset of the normalizing, you should get this:

user table

+----+--------+
| id | name   |
+----+--------+
|  1 | Alice  |
|  2 | Bob    |
|  3 | Carlos |
|  4 | Dan    |
|  5 | Erin   |
|  6 | Frank  |
+----+--------+

server table

+----+----------+
| id | name     |
+---------------+
|  1 | server01 |
|  2 | server02 |
|  3 | server03 |
+----+----------+

booking table

+----------+--------+---------------------+---------------------+
| serverid | userid | start               | end                 |
+----------+--------+---------------------+---------------------+
| 1        | 1      | 2013-10-28 00:00:00 | 2013-12-31 00:00:00 |
| 1        | 2      | 2013-10-28 00:00:00 | 2013-12-31 00:00:00 |
| 2        | 3      | 2013-10-28 00:00:00 | 2013-12-31 00:00:00 |
| 3        | 4      | 2013-10-28 00:00:00 | 2013-12-31 00:00:00 |
| 3        | 5      | 2013-10-28 00:00:00 | 2013-12-31 00:00:00 |
| 3        | 6      | 2013-10-28 00:00:00 | 2013-12-31 00:00:00 |
+----------+--------+---------------------+---------------------+

You could create indexes on the booking tables like this:

ALTER TABLE booking
    ADD INDEX user_server_ndx (userid,serverid),
    ADD INDEX server_user_ndx (serverid,userid),
    ADD INDEX start_end_ndx  (start,end),
    ADD INDEX end_ndx (end)
;

Perhaps you do not need all these indexes, but you can decide which ones are not needed.

Answer 2

I would overthink your design and would change to something like this:

Usertable

+--------+----------+
| UserID | Username |
+--------+----------+
|    1   |   PAUL   |
+--------+----------+
|    2   |   Eric   |
+--------+----------+

Servertable

+------------+------------------+
|  ServerID  |    Servername    |
+------------+------------------+
|     1      |      Apache      |
+------------+------------------+
|     2      |      Nginx       |
+------------+------------------+

Serverbooking

+-------------+-----------------+---------------+-------------+
|   ServerID  |     UserID      |      Start    |     End     |
+-------------+-----------------+---------------+-------------+
|      1      |        1        |   somedate    |   somedate  |
+-------------+-----------------+---------------+-------------+
|      2      |        1        |   somedate    |   somedate  |
+-------------+-----------------+---------------+-------------+
|      1      |        2        |   somedate    |   somedate  |
+-------------+-----------------+---------------+-------------+