Database Design - Application Integration with Other Systems

Question

I'm currently integrating external applications in my app ex SalesForce.com. My question pertains to properly designing tables for data capturing custom fields.

So for SalesForce all companies have an Organization ID but then they can also have custom fields for capturing data. I've looked at two options for handling this.

1. Let them create their fields serialize the names as keys in an array. Then connect the values into that array and re serialize.
2. Create 10+ columns in the db that allow for custom values(field names) and then map the users data to those fields.

Which one of these approaches is more efficient and maintainable for the long run?

Answer 1

It's not clear from your question whether you need these custom fields on a per-user or per-table basis. If it's per table, then I'd create a "sibling" table for each table that required the additional fields. Give it the same primary key columns as the main table, plus a sequence number column and columns for a field name and value. I'd make them both character columns and plan to convert to/from the actual field type, but if you want to create a separate column for each data type and only use one, that's your call. Then, whenever you fetch a row from the main table, you also fetch all the rows with the same key from the sibling table, and order the returned columns by sequence number. You can add additional columns to the sibling table as required; e.g., you may want to add a flag to indicate that certain fields are required.

If you want to create all the entries in the sibling table for each row inserted into the main table, then you might want to have a "template" or "prototype" row with a special key in the main table, so you can easily get all the custom fields. This also permits you to specify default values for the custom fields, just store them in the prototype entries.