Custom fields

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Custom fields

Post by alex.barylski »

Have you ever designed an application where you required the user to have the ability to create custom fields?

I would like both theory and technical disscussion to follow.

Peesonally I can see two potential methods:

1. You actually ALTER the table with the appropriate fields adding/removing as required
2. You store custom fields in a separate table something like below:

Code: Select all

pkid, table_name, field_type, field_name, field_value
Ideally custom fields would be searchable and even better sortable/filtering, etc...which is why I like option one best. Option twao might not scale well although it's probably easiet to manage.

Lastly (a technique I've used in the past) was to store custom fields in a single text field for each row:

Code: Select all

pkid, fname, lname, age, sex, custom
custom would then look something like JSON or similar and hold all data nessecary, of course this is only good for data storage not searching or manipulation...

So I'd like to hear you ideas, opinions on best practice, experiences on past adventures, etc...

Cheers :)
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Custom fields

Post by Benjamin »

It would really depend on the requirements for the application. If it didn't need to be searchable I would just serialize->compress and store in a blob - most likely. Then I could store unlimited fields.

If it needed to be searchable maybe create a second table containing the user_id, field_name, field_value. Then each user can have as many custom fields as they want, it's clean and searchable.
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: Custom fields

Post by alex.barylski »

The latter OK my concern is with display...

How difficult will it be to display those custom fields in a table side by side with the actual fields and have it so when someone sorts by say "address2" all the other records go with it.

[sql]SELECT * FROM table1, table2 WHERE table1.pkid = table2.fkid AND table2.userid = 1 ORDER BY table2.address2[/sql]

I do like the idea of user's having their own fields though...which would be pointless trying to implement using dynamic schema...

You know what...I think you have me sold on using the fields table... :)
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Custom fields

Post by Benjamin »

I don't know if I understand what you're asking. If there is more than 1 custom field per user I don't think you would be able to retrieve them without a separate query. A union might work but I doubt it.
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: Custom fields

Post by alex.barylski »

Well obviously if there custom fields and each user has their own...then chances are there will be more than one field, right. ;)

Anyways...why would I need a UNION and not just SELECT all fields for a given user?

Searching, Sorting, Filtering, etc should all be possible...at least with what I understand of the problem right now...what am I missing?

Sorry to make you think for me...but I'm quite tired as I didn't sleep well last night. :)
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Custom fields

Post by Benjamin »

I'm saying that if you want to get the standard fields from the user table, and the custom fields from the field table, you would probably need two separate queries.
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: Custom fields

Post by alex.barylski »

I'm saying that if you want to get the standard fields from the user table, and the custom fields from the field table, you would probably need two separate queries.
Why would I need two separate queries? Yuck. I need them in one query otherwise I'd loose sorting and filtering...

Code: Select all

contacts:
pkid, fname, lname, age, sex
 
contacts_custom:
pkid, fkid, field_name, field_type, field_length, field_value
[sql]SELECT * FROM contacts, contacts_custom WHERE contacts.age < 30 AND contacts_custom.fkid = contacts.pkid ORDER BY contacts_custom.field_name = 'last' ASC[/sql]

Should the above not return a result as a single table with the custom fields associated with each record?

Admiitedly I haven't put much thought into the above SQL so I may be wrong...but I'm curious to know what I'm missing...as it doesn't seem like a complex task...
Post Reply