[brlav35]

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

[pootle flump]

Is the gist of this that you want users to be able to do point-in-time reporting?

[brlav35]

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...

[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?

[brlav35]

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.

[pootle flump]

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.

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.

[brlav35]

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?

[pootle flump]

One user can only save one set of values per form - correct?

[brlav35]

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