dynamically add custom field to database

[MrMastermind]

Hi,

 

I'm trying to figure out how to approach a way to have users create custom fields in an application.

 

Any suggestions on how to approach this? Should I add the field directly to the table or should it be in a relational table?

 

Thanks.

[Demonic]

You could create a field in the users table, that contains a serialized array and the serialized array could be sorted like:

 

Array( Field Name => Array (

                                - Field Options

                                - Field Type

                                - Field Access ( On/Off )

                                    ),

)

 

^peusdo code

[akitchin]

what kinds of fields are we talking about here?  if you ever have to add a column to a table, in my opinion, you've designed the system wrong.  you would probably want to have a separate table for custom fields with columns that describe each field and assign it to the relevant user.

[MrMastermind]

Akitchin,

 

The design of the system will be fine. But as I said, I want a generic approach. One client wants contacts with only an e-mailaddress other clients might also want to store other information like name, gender, birthday, etc... As I want the user to be able to add any kind of field to the contact I need to be able to add the fields.

All custom fields will apply to all contacts added though.

 

So, would I create the fields in a seperate table, or add them to the "contacts" table?

[akitchin]

you will want to place them in a separate table.  adding columns to a table is unwise, mostly because you never know how bloated the structure will eventually become.  a structure like this one would probably be helpful:

 

user_id
field_id
field_name
field_type
field_validation

 

to store the contacts, you could use a table like this:

 

user_id
contact_id
field_id
field_value

 

to grab the contact information, you can group by contact_id to get all of the values for that contact.  alternatively, as demonic mentioned, you could store the contact info as a serialized array in one field:

 

user_id
contact_id
serialized_info

 

the only issue with storing as serialized array is that you won't be able to offer the users much power for searching their contacts.

[Demonic]

you will want to place them in a separate table.  adding columns to a table is unwise, mostly because you never know how bloated the structure will eventually become.  a structure like this one would probably be helpful:

 

user_id
field_id
field_name
field_type
field_validation

 

to store the contacts, you could use a table like this:

 

user_id
contact_id
field_id
field_value

 

to grab the contact information, you can group by contact_id to get all of the values for that contact.  alternatively, as demonic mentioned, you could store the contact info as a serialized array in one field:

 

user_id
contact_id
serialized_info

 

the only issue with storing as serialized array is that you won't be able to offer the users much power for searching their contacts.

 

Thanks note taken

[MrMastermind]

Thanks for the clear explanation! That's exactly what I needed.

 

Thanks again!