Welcome Guest, Not a member yet? Register   Sign In
Best solution for custom / unplanned fields in database?
#1

[eluser]Skinnpenal[/eluser]
Hi!

I keep having the need save custom data in the database. It could be the data from different page types in a cms etc., basically forms that the users can build themselves.

What is the best way to do this?

What I've used so far is to have three tables:
Field group - groups a set of field intos a complete form
Field settings - defines the field names, rules, type etc.
Field data - holds the data submitted for the fields

The Field data table would have these columns:

id (int) | page_id (int) | field_id (int) | data (text)

This has worked very well so far, but the concern is that the data column for the field data table has to have a data type in the database that would work for all types of data, so I've ended up with Text. Meaning that even if it's a field that will hold a tiny string or number, it's always the big data type Text. And I'm sure there are more flaws to this solution.

The only thing I can think of to solve this is to have a table for each "field group" and then actually modify that when adding/removing columns. But is this any good?
#2

[eluser]xzela[/eluser]
No, adding or removing columns on the fly is dangerous. It can lead to corrupted databases. Modifying the structure the database is generally a bad idea unless you are doing controlled maintenance (preferably with no users logged in currently writing to tables).

I have often thought about this idea (adding custom fields) myself. What I think you can do to solve this type issue is to cast the fields based on their data_type. It's easier/safer to do this in strongly-typed language like Java.
http://java.sun.com/docs/books/tutorial/...types.html

PHP is not known for it's strong data types but it can be done. I'm not sure if this helps.

Best of luck on your project.
#3

[eluser]Skinnpenal[/eluser]
Thanks for your reply, xzela. Now I know not to mess with the database Smile

Another solution could be to have a table per field type. I think Concrete5 uses this approach.

The downside of that would be that each page would potentially require tons on joins to get all the data out. And a little less convenient to develope, as aposed to just putting it all in one table.
#4

[eluser]TheFuzzy0ne[/eluser]
I see no problem with having two other tables. One to store the names of the new fields with ID, and one to store the data with field ID. With two two-column tables, you never have to change the structure of your database, only add rows.

EDIT: Crap, I missed your last reply.




Theme © iAndrew 2016 - Forum software by © MyBB