I have an "interesting" problem here - in a given mysql database scheme some sql wizard used comma-separated values in a text-field and with that values lookups have to be done. The data itself is simple numbers like 1,34,25,66,78,134 and so on. So what I have is something like this:
id | username | groups | more... --------------------------------------------- 1 | name | 1,23,4,55,6 | ... 2 | name2 | 3,2,4,5 | ... The groups string can be very long. there is also a table "groups" with "id, groupname", as expected, from which the lookup has do be done.. Yes, this is bad design. No, I can not immediately change the design, I will try to, but atm I have to handle the situation as it is. Of course normally this would be solved with an intersection table and a many-to-many relation. For anybody interested: there is a book by Bill Karwin called "SQL Antipatterns" - he names the described design Jaywalking and it is the first antipattern in the book. I was surprised to find it in real life.... So my question is: how to handle this with web2py? I really would like to use web2py to build a nice management interface on top of that tables, but currently I do not know how to go on with that jaywalking antipattern in my way. My first idea was to create my own intersection table - but the process of [re-]converting data does only work in a static environment - the data is heavily used and so transformations of tabledata would be neccessary on every request to mirror the live situation...too slow! So I am looking for a good way to implement some kind of layer that would translate this field with comma-delimited data into something that can be used with web2py crud forms. What do you think? How to handle this? Thank you very much for your attention! Snaky
