Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Database schema advice for storing form fields and field values

I've been tasked with creating an application that allows users the ability to enter data into a web form that will be saved and then eventually used to populate pdf form fields.

I'm having trouble trying to think of a good way to store the field values in a database as the forms will be dynamic (based on pdf fields).

In the app itself I will pass data around in a hash table (fieldname, fieldvalue) but I don't know the best way to convert the hash to db values.

I'm using MS SQL server 2000 and asp.net webforms. Has anyone worked on something similar?

like image 668
pteranodonjohn Avatar asked Nov 18 '11 18:11

pteranodonjohn


People also ask

What makes a good database schema?

A good database design is, therefore, one that: Divides your information into subject-based tables to reduce redundant data. Provides Access with the information it requires to join the information in the tables together as needed. Helps support and ensure the accuracy and integrity of your information.

What are the 3 types of database schema?

Schema is of three types: Logical Schema, Physical Schema and view Schema. Logical Schema – It describes the database designed at logical level. Physical Schema – It describes the database designed at physical level. View Schema – It defines the design of the database at the view level.

What type of data you should not store in a database?

Finally, you shouldn't store credit card information in your database unless you absolutely need to. This includes credit card owner names, numbers, CVV numbers, and expiration dates.

What is the purpose of database schema?

A database schema defines how data is organized within a relational database; this is inclusive of logical constraints such as, table names, fields, data types, and the relationships between these entities.


2 Answers

Have you considered using a document database here? This is just the sort of problem they solve alot better than traditional RDBMS solutions. Personally, I'm a big fan of RavenDb. Another pretty decent option is CouchDb. I'd avoid MongoDb as it really isn't a safe place for data in it's current implementation.

Even if you can't use a document database, you can make SQL pretend to be one by setting up your tables to have some metadata in traditional columns with a payload field that is serialized XML or json. This will let you search on metadata while staying out of EAV-land. EAV-land is a horrible place to be.

UPDATE

I'm not sure if a good guide exists, but the concept is pretty simple. The basic idea is to break out the parts you want to query on into "normal" columns in a table -- this lets you query in standard manners. When you find the record(s) you want, you can then grab the CLOB and deserialize it as appropriate. In your case you would have a table that looked something like:

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

A few protips:
a) use a text based serialization routine. Gives you a fighting chance to fix data errors and really helps debugging.
b) For SQL 2000, you might want to consider breaking the CLOB (TEXT field holding your payload data) into a separate table. Its been a long time since I used SQL 2000, but my recollection is using TEXT columns did bad things to tables.

like image 160
Wyatt Barnett Avatar answered Sep 25 '22 15:09

Wyatt Barnett


The solution for what you're describing is called Entity Attribute Value (EAV) and this model can be a royal pain to deal with. So you should limit as much as possible your usage of this.

For example are there fields that are almost always in the forms (First Name, Last Name, Email etc) then you should put them in a table as fields.

The reason for this is because if you don't somebody sooner or later is going to realize that they have these names and emails and ask you to build this query

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

when you could have written this

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

Also get off of SQL 2000 as soon as you can because you're going to really miss the UNPIVOT clause

Its also probably not a bad idea to look at previous SO EAV questions to give you an idea of problems that people have encountered in the past

like image 45
Conrad Frix Avatar answered Sep 24 '22 15:09

Conrad Frix