Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2008
    Location
    Québec City, Canada
    Posts
    10

    Question Best way to store user-saved searches/forms

    Hello,

    in our project, we want to give to our users ability to save form fields values for later use. It will be named the fast OR saved searches.

    We have a relatively huge number of search forms, each one with specific fields. So I don't want, if possible, to use a distinct table for each form.

    The good and logic way to design the table for this purpose looks like :
    SAVED_USERS_FORMS (
    USER_ID NUMBER,
    -- corresponding user
    FORM_ID NUMBER,
    -- system wide unique form identificator (maybe varchar2 code rather than number?)
    SAVED_NAME VARCHAR2,
    -- name of the shorcut/saved form, by user

    FIELDS_VALUES ???????,
    -- saved fields key/values for PK (user, form, name) combination

    PRIMARY KEY (USER_ID, FORM_ID, SAVED_NAME)
    );

    The hesitation here is : how to store the fields & values pairs?
    - XML field?
    - our custom text structure and formating representing something key => value
    - our custom serialized java object into a field

    I think that the EAV modelling technique approach can be a flexible way to achieve our goal, but as I read on the net there's an important set of downsides with this approach. But, this way, my table is something like:

    SAVED_USERS_FORMS (
    USER_ID NUMBER,
    FORM_ID NUMBER,
    SAVED_NAME VARCHAR2,

    FIELD_NAME VARCHAR2,
    FIELD_VALUE ????,
    -- saved field key/values for PK (user, form, name, field name) combination

    PRIMARY KEY (USER_ID, FORM_ID, SAVED_NAME, FIELD_NAME)
    );

    So the new problem can be the field FIELD_VALUE type enforcement. Do we need to have a field for each data type, so validation complexity can increase dramatically...

    Do we need or is suggested to have in a way or another metadata tables describing supported form fields, datatypes, etc?

    Other things to take into account:
    * simple and scalable solution, heh, ye, please.
    * what happens if the form evolve over time? the final solution must not crash or cause some kind of inconsistencies.
    * ..... others ..... ?

    Other info: we're using Oracle Database 10g, JDEV, JSF, ADF, Business Components.

    thanks for any help or comments
    Bruno

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Is the gist of this that you want users to be able to do point-in-time reporting?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2008
    Location
    Québec City, Canada
    Posts
    10
    Quote Originally Posted by pootle flump
    Is the gist of this that you want users to be able to do point-in-time reporting?
    hum, I don't understand very well what you mean by GIST... it's not for reporting, just to save search parameters for later re-useability...

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - so you don't want to be able to reproduce the data they viewed. You want to be able to reproduce the queries?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2008
    Location
    Québec City, Canada
    Posts
    10
    Quote Originally Posted by pootle flump
    Ok - so you don't want to be able to reproduce the data they viewed. You want to be able to reproduce the queries?
    yes, exactly, this is to refill the saved web form fields values , and then after, the user press the search button.


  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I would go with EAV. I would not even call it EAV. Your domain is what is in the form fields. As such, I would not try to constrain data types either. Assuming the only "reporting" you want to do is rebuild a form then, more than likely, the SQL is trivial and retrieval should be fast.
    Quote Originally Posted by brlav35
    * what happens if the form evolve over time? the final solution must not crash or cause some kind of inconsistencies.
    In this case your solution will not work. You are tightly coupling your UI and data with this design. You can't get away from it - if one is inconsistent with the other it won't work. Preventing crashes is just part of your application logic.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jul 2008
    Location
    Québec City, Canada
    Posts
    10
    Ok,

    to me more rock solid, I'll go with this kind of data model:

    SEARCH_FORM (
    FORM_ID,
    DESCR,
    ...
    PK(FORM_ID)
    );

    SEARCH_FORM_FIELDS (
    FORM_ID,
    FIELD_NAME,
    ...
    FK(FORM_ID -> SEARCH_FORM.FORM_ID)
    PK(FORM_ID, FIELD_NAME)
    );

    SAVED_USER_FORM_FIELDS (
    USER_ID,
    FORM_ID,
    FIELD_NAME,
    FIELD_VALUE

    FK(USER_ID -> USERS.USER_ID)
    FK(FORM_ID,FIELD_NAME -> SEARCH_FORM_FIELDS.FORM_ID,FIELD_NAME)

    PK(USER_ID,FORM_ID,FIELD_NAME)
    );


    With this, we can easily ensure solidity by explicitly identifying the supported forms and forms fields. If we drop support for one, just modify our app. side and delete cascade the SEARCH_FORMS or SEARCH_FORM_FIELDS...

    For retrieval, we generally query the db with the query:
    SELECT *
    FROM SAVED_USER_FORM_FIELDS
    WHERE USER_ID = :userid
    AND FORM_ID = :formid

    and store the result in an array or other kind of data structure. For these reasons, EAV can be appropriate cauz we don't make any analysis or search based on a combinations of differrent attributes/values, etc.

    So, is it really a good idea to have metadata tables (SEARCH_FORM and SEARCH_FORM_FIELDS)? Only in sake of consistency... versus performance?

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    One user can only save one set of values per form - correct?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jul 2008
    Location
    Québec City, Canada
    Posts
    10
    Quote Originally Posted by pootle flump
    One user can only save one set of values per form - correct?

    No! sorry, he can save more than one fast-search per form. The goal is that users can save multiple time, under different identification names, values for the same form.

    for this we should add a SAVED_NAME (entered by users) to the SAVED_USER_FORM_FIELDS table, and is part of the PK:
    PK(USER_ID,FORM_ID,SAVED_NAME,FIELD_NAME)

    For retrieval, it becomes:
    SELECT *
    FROM SAVED_USER_FORM_FIELDS
    WHERE USER_ID = :userid
    AND FORM_ID = :formid
    AND SAVED_NAME = :savedname

Posting Permissions

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