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.wikipedia.org/wiki/Tree_structure
> -D
