0

I currently have a database structure for dynamic forms as such: 

grants_app_id  user_id  field_name  field_value 
5--------------42434----full_name---John Doe  
5--------------42434----title-------Programmer  
5--------------42434----email-------example@example.com

I found this to be very difficult to manage, and it filled up the number rows in the database very quickly. I have different field_names that can vary up to 78 rows, so it proved to be very costly when making updates to the field_values or simply searching them. I would like to combine the rows and use either json or php serialize to greatly reduce the impact on the database. Does anyone have any advice on how I should approach this? Thank you!

This would be the expected output: 

grants_app_id  user_id  data
5--------------42434----{"full_name":"John Doe", "title":"Programmer", "email":"example@example.com"}
ashin999
  • 375
  • 1
  • 12
  • 1
    You will be going from bad to worse. Use relational tables. In this case you should have a users table and relate that to tables with data related to users. – AbraCadaver Apr 23 '14 at 19:41
  • I already do have a users table. This is for form information when others submit forms. However, every form is different in the inputs that they have. – ashin999 Apr 23 '14 at 19:46
  • I actually see no problem with this approach if what you are looking to do is store data without a specific schema definition AND (this is important) you have no need to look up records in the database based on specific field values. that being said, this is in essence what NoSQL storage is designed to do, so that might be a better storage medium for this use case. – Mike Brant Apr 23 '14 at 19:50
  • I would love to use NoSQL storage, but I don't have that much flexibility in terms of what my company allows, so I'm simply trying to work with what I've got. – ashin999 Apr 23 '14 at 19:52
  • What is the exact problem with "_and it filled up the number rows in the database very quickly_"? – AbraCadaver Apr 23 '14 at 19:57
  • The current method I am using, simply takes all the inputs within a form and places the input name into field_name and the value into field_value. If a form has 78 inputs, for example, it can be quite costly in terms of attempting to search for a value and updating. In the past month, I accrued roughly 28,000 rows, for something I could only need 518 rows. See what I mean? – ashin999 Apr 23 '14 at 20:01
  • You can speed up searching significantly by adding indexes for relevant fields. I also have to agree with @AbraCadaver that you really ought to be using relational tables. It takes more planning and development, but it will pay off in performance and accessibility. It's just how SQL is meant to be. – Recovering Nerdaholic Apr 23 '14 at 21:35

1 Answers1

0

It seems you don't have a simple primary key in those rows.

Speeding up the current solution:

  • create an index for (grants_app_id, user_id)
  • add an auto-incrementing primary key
  • switch from field_name to field_id

The index will make retrieving full-forms a lot more fun (while taking a bit extra time on insert).

The primary key allow you to update a row by specifying a single value backed by a unique index, which should generally be really fast.

You probably already have some definition of fields. Add integer-IDs and use them to speed up the process as less data is stored, compared, indexed, ...

Switching to a JSON-Encoded variant

Converting arrays to JSON and back can be done by using json_encode and json_decode since PHP 5.2.

How can you switch to JSON?

Possibly the current best way would be to use a PHP-Script (or similar) to retrieve all data from the old table, group it correctly and insert it into a fresh table. Afterwards you may switch names, ... This is an offline approach.

An alternative would be to add a new column and indicate by field_name=NULL that the new column contains the data. Afterwards you are free to convert data at any time or store only new data as JSON.

Use JSON?

While certainly it is tempting to have all data in one row there are somethings to remember:

  • with all fields preserved in a single text-field searching for a value inside a field may become a two-phase approach, as a % inside any LIKE can skip into other field's values. Also LIKE '%field:value%' is not easily optimized by indexing the column.
  • changing a single field means updating all stored fields. As long as you are sure only one process changes the data at any given time this is ok, otherwise there tend to be more problems.
  • JSON-column needs to be big enough to hold field-names + values + separators. This can be a lot. Also if you miss-calculate a long value in any field means a truncation with the risk of loosing all information on all fields after the long value

So in your case even with 78 different fields it may still be better two have a row per formular user and field. (It may even turn out that JSON is more practicable for formulars with few fields).

As explained in this question you have to remember that JSON is only some other text to MySQL.

Community
  • 1
  • 1
TheConstructor
  • 4,285
  • 1
  • 31
  • 52