What's the preferred method for providing customizable categories to a database?

Question

For example, I have a contacts database that has some basic information about a contact. First name, last name, phone number, etc.

Although my options can encompass some of the obvious contact details, it would need to have a method by which users could add their own custom fields, like 'website', 'widget_1', and so on.

My first thought was to add some miscellaneous columns in the contacts table after first_name, last_name, etc., and making them a large varchar data type so somebody could store any information there. That seems sloppy and even still we couldn't expand contact details past the number of miscellaneous fields.

Optimally I'd like a user to click something like 'add a custom field', then populate it with data. What is an intelligent method of doing this without muddying the database?

Answer 1

The easiest way to do this is to have another table that looks something like this:

contact_id INT PK, field_name VARCHAR(64) PK, field_value TEXT

You can have the contact_id field be a foreign key to your contacts table, then join on this table whenever you want to read in the custom fields.

Answer 2

There's no good solution that really fits the relational database paradigm. Allowing each user to pick his or her custom fields to supplement the conventional columns fundamentally breaks the definition of a relation.

Nevertheless, what you describe is a common requirement of data-driven applications. I did a presentation showing options, and their pros and cons: Extensible Data Modeling

You may also like to read my answer to Product table, many kinds of product, each product has many parameters, which is a similar data management problem.

All of the solutions seem more or less clumsy in SQL, because SQL wasn't designed for this task. That's why some people are attracted by NoSQL solutions. But by doing so, they give up some of the good advantages that SQL has, for instance table headings, constraints, and proper data types.