Jump to content
Larry Ullman's Book Forums

Ability For Custom Checkout Fields


Recommended Posts

Hi Larry,

 

I am looking to build a ticketing system for a company which arranges business networking events.

 

Most of the bases I need to cover are thoroughly explained in the book.

 

Where I am stuck is that I need to create the ability for the company to add custom fields to the checkout/registration form depending on the ticket purchased. For example, some events require the attendees to submit a detailed employment history. So I need to create the option for the company to add extra fields to the checkout as needed per the ticket selected for purchase.

 

I am not sure how to tackle this from MySQL's perspective.

 

 

I can see two scenario's:

1) Create table custom_fields which would store the properties of the custom fields (type -textarea/checkbox/radio, size, maxlength, validation rule etc) with a foreign key to the ticket item. Then create table custom_field_data which would store the actual values entered by the user, with a foreign key to custom_fields and another FK to the order id. The downside here is that when the company adds more than one custom field to a ticket item, I will need to retreive multiple rows from MySQL for a single ticket order, which seems very messy to handle.

2) Scenario two would be to create table custom_fields which would store the field properties as above, and then generate a new table per ticket, with that table storing all the custom data entered. This would solve the issue of MySQL returning multiple rows, as now it will only be returning one row via a join, but the downside here is that 5 years down the line the database will have 1000 tables. Hardly a good idea.

 

Any suggestions on a better way to do this?

 

Much thanks

David

Link to comment
Share on other sites

Hi David,

 

Initial thoughts are that 2 is definitely not the way to go not scalable and a nightmare to manipulate etc...

 

Personally I'd either run with your first option or I'd go down the same route wordpress do with custom fields by storing the custom fields in an array, serialising (or json encoding) the array and storing key value pairs in a separate table. I guess the deciding factor between the two techniques would be how the data needs to be interrogated.

 

If you're only extracting and displaying the custom fields I'd use the serialisation technique - if you need to interrogate the values of those custom fields then your first idea would be my choice.

 

Hope that helps

  • Upvote 1
Link to comment
Share on other sites

I generally agree with Stuart here, although an alternative would be to create a custom_fields table, with the various properties, and a custom_fileds_data table. That table would store the custom_field_id, the user's data, and a link to the other table that stores the non-custom user data.

  • Upvote 1
Link to comment
Share on other sites

 Share

×
×
  • Create New...