1

We're working a web application (Ruby/Rails + Backbone,jQuery,Javascript) where a user can manage a booklist and drag and drop books to rearrange their order within the list, which has to be persisted.

We have books and a custom collection of books called booklist, for which we have two tables: book and booklist. Since a book could belong to multiple booklists, and a booklist consists of multiple books, they have an m x n relationship, and we have another additional table to store the mapping. Lets say we use this for all purposes. Now when the user wants to re-order the books in her bookshelf, we'd need to store that order.

I can totally see the sense about why storing ids in a column is evil , no doubts about it. What if we have the tables normalized, and for all other cases we'd go through the standard operations.

There are quite a few approaches on storing an additional order column. But still it seems like bad design to store the ids of the books in a booklist in a comma separated list in the booklist table, even assuming that integrity is maintained.

We'd never run into this...

SELECT * FROM users WHERE... OH F@$%CK -

Yes it's bad, you can't order, count, sum (etc) or even do a simple report without depending on a top level language.

because we'd simply be selecting books based on the booklist id using the join table like the standard approach. (In any case, we're only getting the books as an array as part of the backbone booklist model)

So what if we retrieve the booklist and books for the booklist, and do the sorting programatically on the client side (in this case Javascript?) based on the CSV column.

It appears to be a simple solution because:

  • Every time the user reorders a book, we simply store all the ids in this one column freshly again. (A user will have at the most 20 to 30 books in a booklist).
  • We could of course simply ignore invalid ids, i.e. books that have been deleted after the booklist had been created.

What are the disadvantages of this approach, which seems to be simpler than maintaining the sort order and updating other columns every time an order is changed, or using a float or weightage, etc.

Community
  • 1
  • 1
msanjay
  • 2,255
  • 2
  • 19
  • 19
  • wanted to post this earlier but I'm going offline for a couple of weeks, so please excuse me for being unresponsive till then – msanjay Mar 19 '14 at 09:10

1 Answers1

1

As per my knowldege its really violating the rule of RDBMS.Which causes facing many difficulties when applying JOIN.

Hope it will help you.

JDGuide
  • 6,239
  • 12
  • 46
  • 64
  • thanks - as I said, we won't be using this column for any kind of a join operation, we'd be going through the standard SQL approach. As we're using backbone and rails, this is done rather implicitly. – msanjay Mar 19 '14 at 09:23
  • Then its fine.But you need to update the values always. – JDGuide Mar 19 '14 at 09:24
  • right, I'll always be updating the CSV column every time. The list of books for a booklist is already available locally as a Javascript array (part of the booklist backbone model) I'd be mapping the CSV value of ids to the array of books, to set the order rendered in the UI – msanjay Mar 19 '14 at 10:04