1

I want to add dynamic columns in a mysql table but I don't 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 it's an integer or a string. I want to have the possibility to have it dynamic, let the user choose.

How can I do this?

Thanks!

RichN
  • 6,181
  • 3
  • 30
  • 38
never_had_a_name
  • 90,630
  • 105
  • 267
  • 383

6 Answers6

4

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.

  • 1
    I have no idea :) Actually, MySQL can store an INT in a text field, so you may just need a text field. You can even sort by an INT in a text field using "SORT BY colname+0" –  May 31 '10 at 19:43
2

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.

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
1

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.

Jaime
  • 6,736
  • 1
  • 26
  • 42
1

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.

dnagirl
  • 20,196
  • 13
  • 80
  • 123
0

Finally it's here MySQL select where JSON field property has value . Save your data in json column and let mysql do the magic

WiZDON Systems
  • 1
-2

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.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345