0

I know questions of this type have been asked here, but wondering if this scenario is do-able (I didn't see other examples of this).

Let's say I have a MySQL DB table that has the following items:

 item  |  type
----------------
   1   |  Small
   2   |  Small
   3   |  Large
   4   |  Small

And I have an order table where an end-user can "check-out" these items for a date range that he/she specifies (sort of like booking a hotel room):

 orderid  |  item  |  startdate  |  enddate
--------------------------------------------
    1        2,4      2015-08-15   2015-09-15

Potentially, there can be thousands of items, and anyone can choose to reserve a large number at once if desired. This is why I represent item as a string 2,4 in the order table example above.

Assuming the end-user were to pick a date range within the orderid's date range, how can I do a MySQL query that only shows items which are available outside the start/end date range when I represent the item as a string in the order table? Would this be possible?

Wes
  • 724
  • 1
  • 11
  • 29
  • Storing items ID list as comma separated varchar is not a good practice, you should create separate table for connecting items and order tables I guess. However for selecting items ID from item column in your order table, you should use FIND_IN_SET function https://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set – Ondřej Šotek Aug 15 '15 at 06:09
  • So do you mean, if another user pick a date between `2015-08-15` and `2015-09-15` you need to show him that only `1` and `3` items are available? – Chaya Sandamali Aug 15 '15 at 06:10
  • That's right chayasan. @Ondrej - but wouldn't having a third table like that become huge if multiple people were to order say 500 items at once, not to mention that the same item could be ordered again and again if the date range is available? – Wes Aug 15 '15 at 06:31
  • Going off your answer Ondrej for a third table - is there a best practice to keep the table size manageable? One order could potentially write 500 rows depending on the amount of items reserved - and a reservation can last up to a year. That could get out of hand over time with multiple orders...I agree with you that it would be much easier to go this route, but I worry that the table size would become too large. – Wes Aug 15 '15 at 06:54
  • @Wes It's no problem to have thousands of rows in many-to-many table with proper indexes and it's better instead of having delimited list - more is discussed here http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – Ondřej Šotek Aug 15 '15 at 07:10
  • As others have said, the problem has been made unnecessarily slow and complicated by this lack of normalisation. Fix that, then get back to us. – Strawberry Aug 15 '15 at 07:39
  • Thanks all. I guess DB normalization is the way to go then, which I can figure out. Lesson learned. Appreciate the advice! – Wes Aug 15 '15 at 19:59

0 Answers0