Hi, I am new to web2py and sure not if I am doing things the best way. I am designing an app and would like your opinion on my choices and also how could I implement some stuff in web2py.
My problem: I have "classes of stuff" that I want to store on the database. "Stuff" here is very generic and could be anything. (Not related to OO, it is part of the problem I am modeling). The final end users of my app will be able to select a class to create an "instance", by giving a name to it and selecting options for it. For each class I insert on my database I may have different options available with different values. For example, I may have a class "person" with option "sex" with values "male, female". A user may select this class "person" to create a person, giving a name to it and selecting one value for its sex option. I may have a class "Pie One" on my database, with an option "flavor" with values "flavor 1, flavor 2, flavor 3" and another option "Fruit" with values "apple, banana". I may have another class "Pie Two" also with an option "flavor" but now with different available values for it: "flavor 2, flavor 4" an no option "Fruit" now. When user chooses that it wants a "Pie One", he must also choose a value for its "flavor", but only from available flavor for "Pie One", and also must choose a value for its "fruit" option. If user wants a "Pie Two", the only option is "flavor", there is no "fruit" nor "sex" option for this class. So far, we could have tables like this: Stuff id: name: 1 'person' 2 'pie one' 3 'pie two' Option id: name: 1 'sex' 2 'flavor' 3 'fruit' OptionValue id: option_id: name: 1 1 'male' 2 1 'female' 3 2 'flavor 1' 4 2 'flavor 2' 5 2 'flavor 3' 6 2 'flavor 4' 7 3 'apple' 8 3 'banana' StuffOption stuff_id: option_value: 1 1 1 2 2 3 2 4 2 5 2 7 2 8 3 4 3 6 >From this, I can derive the option names available for each class by its option values (values grouped by option names actually). When user will instantiate a class of "pie one", I want to show him a field "flavor" with a dropdown box with available flavors for this class, and another field "fruit" with fruits available for this class in a dropdown box. I may have many options for each class (or none) and the values available for the same option may be different in different classes, so this has to be generic. How could I show these dropdowns in web2py? There is more. Each of theses classes will be classified in categories, but each one may be in none or more than one category. Also, when I display all classes of a category I must be able to customize the order where the classes appear, and the order will be independent for each category. I thought in implement this with something like: Category id: name: 1 'people' 2 'pies' StuffCategory category_id: stuff_id: position: 2 2 2 2 3 1 I would them retrieve items inverse ordered by position column, and also set this column as autoincrement. This way, when I insert a new class it will always show first on the list by default. If, lets say, I want to insert an item on position 7, before insert it I would do: "UPDATE StuffCategory SET position = position + 1 WHERE position >= 7" and then insert it with its position = 7. It would be similar to move an class to a specific position, update all position values between it and the desired position, before setting the desired position. Would you guys say is this a good way to achieve this functionality or is there a better way? Also, I don't want to write SQL directly, how could I do this nicely in web2py? An additional interface I'd like to provide to admin to change the order would be to list all classes in the right order, and in from of each class, there would be two buttons: up and down arrows. clicking on them would swap its position with the next record. I think this would be great in an ajax interface, but I don't have a clue how to do it in web2py yet. And now, my last problem. Each of these classes will have a number of images associated with them. One of the images of each class will be its main image. Each value may have one of its image associate with it also. For this, I may have an "Image" table: Image id: stuff_id: image: And add a "main_image_id" column to stuff table. I thought of "main_image" column be a reference to image table so it would be easy to change the main image between the images available for that class. For the option values, I could add a column "image_id" also, where the null value would be possible. My problems here are more about the interfaces for inserting new stuff. How to put all this together. For example, I imagine the following possibilities when admin is inserting new stuff to database: - The form for add new stuff and edit new stuff would be the same. - This page would show all fields of stuff, and a list of all images for it. - Main image could be a radio button in front of the images, when the admin inserts the first image for a stuff, it would be the main one. - For the options, it would show a list of option values sorted by option names. - When adding a new option, it would show a list of all option values in the database, sorted by options names. After selecting one, would be possible to also select an image among the images for that stuff or to upload a new one Again, no idea yet how I will glue all this together. I would like to do this in the best web2py style, using SQL Forms for example, but I am not sure yet if it will be possible, considering the complexity of what I am aiming. I really would love your feedback on what I am doing, and nothing of this is final yet, so if you think any of the things I wrote could be done better in another way, please let me know. Later I won't be able to change the design anymore. Kind regards, Fabiano