Geek Answers Handbook

Database schema recommendations for storing form fields and field values

I was instructed to create an application that allows users to enter data into a web form, which will be saved, and then ultimately used to fill in the fields of the PDF form.

I'm having trouble trying to come up with a good way to store field values ​​in a database, because the forms will be dynamic (based on PDF fields).

In the application itself, I will pass the data in the hash table (fieldname, fieldvalue), but I do not know how best to convert the hash to db values.

I am using MS SQL server 2000 and asp.net webforms. Has anyone worked on something like this?

+5
source share
3 answers

Have you considered using a document database here? This is just a problem that they solve much better than traditional solutions for RDBMS. Personally, I am a big fan of RavenDb . Another pretty decent option is CouchDb . I would avoid MongoDb, as this is really not a safe place for data in the current implementation.

Even if you cannot use the document database, you can force SQL to pretend to be one by setting your tables to have metadata in traditional columns with a payload field that is serialized XML or json. This will allow you to search for metadata while staying outside the EAV zone. EAV land is a terrible place.

UPDATE

, , . , , , "" - . , CLOB . , :

SurveyAnswers
  Id INT IDENTITY
  FormId INT
  SubmittedBy VARCHAR(255)
  SubmittedAt DATETIME
  FormData TEXT

:
) . .
b) SQL 2000 CLOB ( TEXT, ) . , SQL 2000, TEXT, .

+4

:

Form
-----
form_id
User
created

FormField
-------
formField_id
form_id
name
value
+1

, , (EAV), , .

, , (, , ..), .

, , - , , ,

     SELECT 
        Fname.value fname,
        LName.Value lname,
        email.Value email,
        ....
     FROM  
         form f
         INNER JOIN formFields fname
         ON f.FormId = ff.FormID
            and AttributeName = 'fname'      
         INNER JOIN formFields lname
         ON f.FormId = ff.FormID
            and AttributeName = 'lname'
         INNER JOIN formFields email
         ON f.FormId = ff.FormID
            and AttributeName = 'email'
         ....


    SELECT 
        common.fname,
        common.lname,
        common.email,
        ....
     FROM  
         form f
         INNER JOIN common c
         on f.FormId = c.FormId

SQL 2000, , UNPIVOT clause

, , SO EAV, ,

+1

More articles:


All Articles