dymanic columns in mysql tables?

Question

i want to add dynamic columns in a mysql table.

but i dont know exactly how.

i want to let the user add some columns (fields) in a thread.

eg. let him add a integer field and a value (eg. price: 199) or a string field and a value (eg. name: teddybear).

the user can add as many field/value-pairs as he wants.

i thought i could create a many-to-many table:

thread <-> thread_field <-> field

thread: id, title
thread_field: field_id, thread_id, value
field: id, name

is this a good structure?

but in this way i have to set a specific column type of thread_field.value. either its an integer or a string. i want to have the possibility to have it dymanic, let the user choose.

how can i do this?

thanks!

Answer 1

The ugly way:

thread_field: field_id, thread_id, value_text, value_int

where value_text is declared TEXT and value_int is declared INT.

For any given entry, you use only one of the two fields.

Answer 2

You can add the type of data to the 'fields' table and store either a string or a binary object and then convert it to the proper type in your code, you would have to do all the validation in code though.

You could also add other properties to the field such as Optional vs Required value, visibility, etc.

Answer 3

This problem comes up very frequently with regards to relational databases. It's part of the definition of a relation that it has a fixed set of attributes, not dynamic attributes.

If you need to allow user-defined attributes in a relational database, the simplest solution is to store a Serialized BLOB such as XML or other semi-structured format (JSON, YAML). You lose the ability to query this efficiently using SQL (unless you use an RDBMS that extends SQL with XML functions and indexes), but you're going to sacrifice many features of the RDBMS no matter how you solve this problem.

Another alternative is to use one of the new non-relational data stores like CouchDB or MongoDB, which makes it easy to extend entities with dynamic, strucutred attributes while remaining somewhat efficient.

Answer 4

If you intend your user to enter arbitrary key=>value pairs, you might want to look at a vertical table. Something like:

post_id, keyname, keyvalue
1, 'name', 'teddybear'
1, 'price', '1.99'
2, 'colour', 'fuchsia'
78, 'mother', 'Diana'
78, 'father', 'Bob'
78, 'pet', 'Fido'

Each of these key/values is linked the the record (post_id) they were created in. This also works reasonably well when you have a lot of options for the user, but very few will ever be chosen. The cons of this solution include a) you can no longer take advantage of automatic typing and b) index is not as useful since the value types are mixed.

If you have a system where the options are well defined, you should design your tables to fit those options. The pros of automatic typing and indexing usually outweigh the flexibility of changing your apparent data structure on the fly.

Answer 5

You shouldn't do it.
Site users has absolutely nothing to do with database structure.

You have to learn how to design database structure properly.
There is not a single site around who uses user-defined dynamic columns in the database.
And I am sure you can manage without it somehow.