Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
1  
Why do you store your data in this way? –  Colin 't Hart Oct 30 '13 at 12:39
1  
Indeed MySQL should crash if you abuse MySQL like this.. –  Raymond Nijland Oct 30 '13 at 22:04

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
1  
What about the function FIND_IN_SET dev.mysql.com/doc/refman/5.0/en/… for normalization problems like this? –  Raymond Nijland Oct 30 '13 at 22:03

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  |
+-------------+-----------------+---------------+-------------+
share|improve this answer
    
+1 for good old-fashion common sense –  RolandoMySQLDBA Oct 30 '13 at 12:50
    
vote up for normalization –  Raymond Nijland Oct 30 '13 at 22:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.