Hi, Maybe the following can help (with respect to one common form from multiple tables)?:
http://www.web2pyslices.com/main/slices/take_slice/102 Good luck, Carlos On Dec 20, 10:47 pm, Fabiano <[email protected]> wrote: > Hi Villas, > > yes, I know this concept, I have a good background in computer > science, but it doesn't fit here, as my data is not hierarchical. > > Anyway, thanks for the suggestion and the links, that Troels articles > seems to be interesting, I will take a look on that sometime. > > My first e-mail was very extensive and I guess not very clear. I am > sorry, I am still learning English. I will try to explain better what > I am trying to do here. > > When you are modeling databases, you usually think of tables as some > sort of classes of stuff and the records are the instances. For > example, a table "Car" is a class of objects, the columns are its > properties, like color, engine, year, model, etc. Each record on this > table would represent a real car, with a specific a color, a specific > year, model, etc. > > You think of the car properties that are relevant for your problem to > select the columns you need when your are designing the tables. If > instead of an inventory of objects you need a list of employees, or > customer, or whatever hole that are people, you may think in > properties like name, birth date, gender, etc. > > The thing is, usually the classes of things that you can store > instances on your database are limited, you usually have one table for > each class of stuff you can store and the properties of these stuff > are pre-defined at design/data modeling time. In normal use, you just > store new records filling the already pre-defined set of properties > (columns). > > What I need to do is to store many different types of stuff on the > same table, not limited to classes of stuff I can foresee in design > phase. The properties of each record will be dynamic. This way, I may > store a "car class" on my db and then say that the properties for this > class are color, engine, model, year, etc. But on my case, I will only > deal with properties which values can be finite sets (so, color is ok, > year is out). You can also think of car properties as car options > (when you buy a car, you have the option to choose its color) or as > variations (two cars may be similar and vary only in color). > > These properties, or variations, or options are what my table "Option" > was about on my first e-mail. "Color" is an option. "OptionValue" > table stores possible values for each option. Like, for color, you may > have values like "blue", "gray" and "white". > > On the same db I may have a class of "chairs", with option "material" > with values "wood" and "metal". I may have an "employee" class with > options "gender" and "department" and its possible values, I may have > "pies", "cakes", "cars", "people" or whatever, just assigning some > properties to each of them and possible properties values.. > > Each of these class may have an arbitrary number of properties, > including none. > > In this design, instead of having a list of properties applicable for > each class ("stuff") I will list option *values* for each, and not > options for each. This way, I may have an option "color" on my db with > lots of color values stored. But when I am creating a class of stuff > "Apple", instead of assign a "color" property to it, I will assign > only the available color options for it, "red" and "green". So, > despite having many more colors in my database, for "apples" I will > have only these two, "red" and "green". "red" and "green" values > refers to the same option, which is color, so I can derive the options > for each class from the list of available option values. If I had > values "blue", "green", "yellow", "small", "big" for a class, I would > derive that the options for this class are "color" and "size" for > example. > > I think the database design is sound for what I need. My main doubts > now are more like how to use this database under web2py and have an > easy interface to the user that will input these stuff on the > database. As there are a lot of relations, I don't want the user to > insert values on each table independently and then link them later. > When he is editing a class of stuff, I want a form that shows all > options and options values for that class and a "new option" button > which would pop up a list of option values (arranged by option names) > that are not already assigned to the class, and also a text input that > permits the user insert a new option and option value that is not > already on the database. > > I think that an interface that puts all this simply and transparently > together is the most complex part. For example, I'd like to have forms > with transparent postback validation as I have with SQLFORM, but I'd > have data from 4 or 5 tables together on the same screen looking like > it is just one thing to the user. > > Thanks for your time and inputs. Kind regards, > Fabiano. > > On 20 dez, 21:26, villas <[email protected]> wrote: > > > > > > > > > Hi Fabiano > > It seems that you have a hierarchical data model. Did you look into > > the idea of nested sets and use a single table which self-references > > using fields 'left' and 'right'. Did you already learn about that > > concept? I mean, perhaps you already considered and dismissed it. > > Some links here which might be > > interesting:http://troels.arvin.dk/db/rdbms/links/#hierarchicalhttp://en.wikipedi... > > -D
