SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Nov 2006
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    User-defined data structures

    Hello all,
    We have an established web-based system that we are looking to expand the functionality of. It uses J2EE, jsp's, Oracle and a bit of flash by the way.

    Say, for example, that we capture basic information about employees such as contact details and equal opportunities data but can't account for every type of data every organisation needs. We now want to allow users to define their own forms to capture whatever data they want. This means writing a system that will dynamically create tables and form SQL queries.

    I'm just trying to research the area a bit so I can try and anticipate some of the issues we'll run into and find the common solutions to common problems. Only thing is I'm having trouble finding any good resources on the subject.

    If anyone could give me one or two pointers it'd be greatly appreciated, or even just some good keywords to search on would be a massive help ;-)

    Cheers
    Dave

  2. #2
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,235
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    You can do this without dynamic table/SQL query creation.

    database has 6 tables:
    forms (id, name)
    questions (id, form_id, name, question_type_id)
    question_types (id, name) ['name' would be something like 'radio', or 'checkbox', whatever kind of field it is]
    question_values(id, question_id, value)
    users (id, username, etc.)
    answers(id, form_id, user_id, question_id, value)

    Now you can store every question, option, and answer without creating tons of database tables.

  3. #3
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,625
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Which version of Oracle? If it is one that supports some native Xml types, that would be the way to go.

    You could then have the standard parts of employee data, then just add an extension object that you serialize to Xml for storage.

  4. #4
    SitePoint Member
    Join Date
    Nov 2006
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot for the swift responses both of you
    Your solution is very similar to one suggested to me elsewhere vgarcia (except your's accounts for form design as well )
    We're using Oracle 10g wwb_99, so it should support native xml types as you suggest? I'll have a look into extension objects, as to be honest I've never touched them before (db stuff isn't my forte )
    Thanks again for the help
    Dave

  5. #5
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,235
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by euclid223
    Your solution is very similar to one suggested to me elsewhere vgarcia (except your's accounts for form design as well )
    Dave
    It's a pretty standard/basic design for a survey/polling/forms app. I've probably written this in 4 or 5 different languages/environments by now

  6. #6
    SitePoint Evangelist superuser2's Avatar
    Join Date
    Aug 2006
    Posts
    598
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you could do something like this:

    have a 'data' table, with id, and field1, field2, field3, etc, etc, maybe 20 or so, then have a 'fieldsets' table with a row for each form. Same structure, but add a 'form' column. Then you can look in here to find out what field14 in "formx" is.

    Hopefully this will help you.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •