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
?
Best Answer
While your
booking
table is in very serious (critical, severe, dire, vital, etc,) need of normalization on theuser
column, here is what you can do in the meantime...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:If you do the reset of the normalizing, you should get this:
user table
server table
booking table
You could create indexes on the
booking
tables like this:Perhaps you do not need all these indexes, but you can decide which ones are not needed.