how to implement database tables with one level of indirection?

[Luis Goncalves]

Hello!

How do I make a database table where the fields are defined by another table?

For example, suppose some users of my system are event organizers, and they can define what fields would belong in a person's profile.

Then, attendees of that event have to fill out a profile with those specific fields.

What is the best way to represent this information (the fields relevant for an event's profile,  and the event profiles of users) in a database?

What I have been thinking of so far is to define a table that holds the definitions of profile fields:

db.define_table('event_profile_field',
    Field('event', db.event),
    Field('display_title','text'),
    Field('data_type', requires=IS_IN_SET('string','text','checkbox','list')),
    Field('display_order', 'int'))

and then each user's profile is built up of multiple entries of the following:

db.define_table('event_profile_entry',
    Field('person', db.person),
    Field('event', db.event),
    Field('event_profile_field', db.event_profile_field),
    Field('data') # XXX we need data to be of different types

However, as indicated above by the comment, I'm not sure if it is possible to store different data types in the event_profile_entry.data field.
(I suppose I could just make it be 'text' type and have the code know that a checkbox can only be True or False, for example).

Is there a more efficient/smarter way to do this?

Thanks,
Luis.

[Richard Vézina]

I think you need inheritance :

http://web2py.com/book/default/chapter/06#Table-Inheritance

Richard

[Luis Goncalves]

Hello Richard!

I looked at this, but wasn't sure how it could help -- what I need is a way for a (non-technical) admin to create profile forms with arbitrary fields (through a friendly web interface), and then users to be able to view and edit their (run-time reconfigurable) profiles.

At any rate, the method I described above seems to work quite well,  thanks to web2py's versatility, allowing me to define forms programmatically (excerpt below).

I was wondering if there was a more clever/efficient/proper way to do so. Perhaps not!

Thanks!!
Luis.

    for field in event_fields:
          # see if person has a pre-defined value
          found = False
          for my_efield in me.event_field:
              if my_efield.display_title == field.display_title:
                  found = True
                  break
 
          if found:
              if field.data_type == 'string':
                  new_input = INPUT(_type = field.data_type, _name = field.id, requires=IS_NOT_EMPTY(), _value=my_efield.data )
                  form[0].insert(-2, TR(field.display_title+':', new_input ))
 
              elif  field.data_type == 'text':
                    .....
          else:
              if field.data_type == 'string':
                  new_input = INPUT(_type = field.data_type, _name = field.id, requires=IS_NOT_EMPTY())
                  form[0].insert(-2, TR(field.display_title+':', new_input ))
 
              elif field.data_type == 'text':
                   ....

[Richard Vézina]

Maybe you could find some inspiration from this project for Django :

http://code.google.com/p/django-dynamic-formset/

You need to install Django to test it...

What you seems to do is adding an arbitrary number of input for a given field...

Following good database design pratice you will normalise your schema... I had try to find a solution similar to django dynamic formset, but I give up in the pass.

You have this thread that could maybe bring some answer : http://groups.google.com/group/web2py/browse_thread/thread/50af0d67554c94d9/ad553c6a5514ecc7?pli=1

Web2py let you do this : http://www.web2py.com/book/default/chapter/07?search=filter#One-form-for-multiple-tables

But you can't have fields with the same name in your table...

Finally it maybe possible with component now to load a arbitrary number of fields inputs for a given table and with jQuery submit the differents forms as one I would investigate in that direction too...

Good luck

Richard

[Luis Goncalves]

Thanks for the links!!

Django dynamic formsets seems powerful!  When I first started off, I investigated using django, but found it very difficult.  Web2py is so much easier to use (especially with the clear manual, and "one click" install with working apps ('Welcome'))!!!

Maybe I'll end up contributing a friendly dynamic form creator for web2py ...

merci,
Luis.

[Richard Vézina]

It surely a missing feature...

Richard

[Bruno Rocha]

The web2py app wizard has a Dynamic model creator, may be you can take a look in to the wizard code. And fork as a plugin.

--

--

Bruno Rocha

[ About me: http://zerp.ly/rochacbruno ]

[ Aprenda a programar: http://CursoDePython.com.br ]

[ O seu aliado nos cuidados com os animais: http://AnimalSystem.com.br ]

[ Consultoria em desenvolvimento web: http://www.blouweb.com ]

[Richard Vézina]

I thougth about it too, but I didn't find the thread...

;-)

Richard

[Anthony]

That type of method will work, but it will result in a new database table being created for each event/custom profile. There are various alternatives for implementing user defined fields -- here are some Stackoverflow links:

 

http://stackoverflow.com/questions/5106335/how-to-design-a-database-for-user-defined-fields

http://stackoverflow.com/questions/4151468/how-to-store-custom-user-fields-in-database

http://stackoverflow.com/questions/2924027/db-design-to-store-custom-fields-for-a-table

 

 

Anthony