[GENERAL] Data base tables design questions for: user saved forms, user parameters
Hello, I need some comments or advices regarding different tables designs approach, for two part of our needs. _*User saved forms*_ 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. The user can save more than one fast-search per search-form, for that he can name it accordingly to the search characteristics. 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 INTEGER,-- corresponding user FORM_ID INTEGER,-- system wide unique form identificator (maybe varchar2 code rather than number?) SAVED_FORM_NAME VARCHAR, -- name of the shorcut/saved form, by user SAVED_FIELDS_VALUES_PAIRS ???, -- 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, in FIELDS_VALUES? - XML field? - our custom text structure and formating representing something key = value - our custom serialized Java object into a field OR I think that the EAV kind of modelling technique 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 INTEGER, FORM_ID INTEGER, SAVED_FORM_NAME VARCHAR, FIELD_NAME VARCHAR, SAVED_FIELD_VALUE , -- saved field 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 SAVED_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 meta data tables describing supported form fields, data types, 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 . ? To enforce SAVED_USERS_FORM to follow defined forms fields, upon adding/delete one or more fields, consistency can be achieved by simple meta data tables describing forms and associated fields. So the design will look: SEARCH_FORMS ( FORM_ID, ... PRIMARY KEY (FORM_ID) ); SEARCH_FORM_FIELDS ( FORM_ID, FIELD_NAME , ... PRIMARY KEY (FORM_ID, FIELD_NAME) ); SAVED_USERS_FORMS ( USER_ID, FORM_ID, SAVED_FORM_NAME, FIELD_NAME, SAVED_FIELD_VALUE, ... PRIMARY KEY (USER_ID, FORM_ID, SAVED_NAME, FIELD_NAME) ); but, this EAV approach isn't perfect because the value field must be a varchar field that we'll possibly store dates, numbers, What is the perfect solution? _*User parameters *_Same kind of problem for storing user system parameters... Is it better to go with one table, 1 row per user with 1 collumn per parameter like: USER_PARAMS ( USER_ID, PARAM1 , PARAM2, PK (USER_ID) ); this way, we can easily enforce the data types per parameters... Or a more flexible approach with a table like: USER_PARAMS ( USER_ID, PARAM_ID, PARAM_VALUE -- FLEXIBLE TYPE? VARCHAR? PK (USER_ID, PARAM_ID) ); USER_PARAMS ( USER_ID, PARAM_ID, PARAM_VALUE_INTEGERINTEGER, PARAM_VALUE_VACHAR VARCHAR(4000), PARAM_VALUE_DATE DATE, ... PK (USER_ID, PARAM_ID) ); like many modellers, it's easy to fall into the generic models easy to maintain but harder to optimize and to ensure consistency? do we need a kind of api for manipulating the parameters, enforcing types, etc? _* *_thanks for any help or comments Bruno
Re: [GENERAL] Data base tables design questions for: user saved forms, user parameters
I'm facing a very similar problem where I work (local govt) where we want to put around 100 forms online. The idea of 100 seperate database tables is too painful to contemplate. Depending on the nature of the data, I suppose, both the structured text string and the serialised object options sound viable. I'm very new here, but I think I've seen mention somewhere in the docs about PG being able to store arrays? If this is true, it might be another option. Mick -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Data base tables design questions for: user saved forms, user parameters
Bruno Lavoie wrote: 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. This is one of the few areas where storing XML in a relational database seems to make sense to me. Forms are well suited to description and validation by XML documents; in fact, with standards like XFORMS there's even a degree of agreement on ways to model and present them. There isn't any particularly strong reason to store the data in a relational DB if you go for XML storage - but if your app already uses PostgreSQL, which it presumably does, then it's much more convenient to be able to use the same access, authentication and storage methods for your form data as everything else. The hesitation here is : how to store the fields values pairs, in FIELDS_VALUES? (1) XML field? I'd certainly be tempted. I'd want to make sure I had good DTDs for my forms, though, and had some sort of form versioning in place to handle detection and conversion or invalidation of old saved form data. Pg's XML support is still pretty rudimentary, but it's handy enough that you can do useful queries on your stored XML data. I'd be highly likely to implement this using (3) if I was tackling the problem. (2) our custom text structure and formating representing something key = value This gets ugly because of the need to store a variety of data types for values. One workaround is to store the values as their textual representations rather than as the real type. That makes any sort of checking and validation even harder than it already is, though, and may also slow things down. (3) our custom serialized Java object into a field I certainly wouldn't want to use any sort of binary object serialization; I'd say that's a recipe for pain and eventual disaster. Serialization to/from XML isn't too bad an idea, though, as Java provides mechanisms for object versioning, etc, and in general makes object-XML (de)serialization surprisingly reasonable. I'd still want a DTD or some other XML schema definition to permit checking of the data in-situ. Personally I think that if you want to store data for may different types of form in one table, and want even rudimentary checking and validation, then XML serialization of Java objects is probably the way to go. * what happens if the form evolve over time? the final solution must not crash or cause some kind of inconsistencies. Proper and careful use of Java's XML serialization should take care of that. It does take extra effort, planning, and thought, but you're signing up for that one way or another by tackling this problem at all. What is the perfect solution? Much like storing generic objects in an RDBMS, the problem isn't a very clean match to the relational model. As such, I doubt there is a perfect solution. I suspect that the most strictly clean approach would be to properly model each form as a table or (more likely) set of related tables. It doesn't sound like that's practical for your particular app, though. XML serialized objects might be the most reasonable compromise. I'll be very interested in the other responses to this, though, and in the resources/articles people reference. like many modellers, it's easy to fall into the generic models easy to maintain but harder to optimize and to ensure consistency? There's a real risk of falling for Enterprise-ey design with system-builder-builders and such. This site: http://thedailywtf.com/ is full of examples of such, including some pretty horrifying database-related ones. For example: http://thedailywtf.com/Articles/The_Enterprise_Rules_Engine.aspx http://thedailywtf.com/Articles/Bitten_by_the_Enterprise_Bug.aspx -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Data base tables design questions for: user saved forms, user parameters
On Fri, Jul 25, 2008 at 8:35 AM, Bruno Lavoie [EMAIL PROTECTED] wrote: The hesitation here is : how to store the fields values pairs, in FIELDS_VALUES? Check out contrib/hstore: http://www.postgresql.org/docs/current/static/hstore.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
