How do you create and save custom custom fields in an SQL database?

Question

I need to allow users to add new entries to the entry, for example. if there is a contact entry, the user may want to add the numerical field "SSN" and the date / calendar field "Date of Birth". Of course, they will do this through an interface.

Then these fields should be writable for all contact entries.

Given that my application runs for many users at the same time (and not for the deployment of one company, etc.), and theoretically, everyone can add their own custom fields, which would be the best practice for storing this information in a database, especially when it’s Need to search?

Answer 1

We add almost all of our applications / products with additional support for attributes / fields for this flexibility for the user.
As we have a product category, in the category a customer can define an additional attribute of any product
what we do at the database level:
The category table has an additional column, for example: Text1Att, Text2Att ... for supporting text values, Num1Att, Num2Att ... for supporting number values, Date1Att, Date2Att ... for supporting date and time values, ID1Att, ID2Att ... support identifier from another table, as you can add a drop-down list, list, ...
here the entire column is of the String data type.
what we store here

we will store meta-information here, for example, for Text1Att meta
PLA; text field; fifty; true False; NULL
Field heading; Type of management; Maximum length; Obligatory field; Spot check required; Custom verification message;
place of birth, text box; one hundred; true true Invalid value
Same thing for a number field ...
for date metadata, the date will look like this:
date of birth, calendar control, true, true, invalid date;
Field signature; Calendar control or may be different; required, is a user check; Custom verification message;

What it does in the product table, adds the same number of columns and has the data type text1Att, .. is varchar, num1Att has a numerical value, date1Att has a date and time, ID1Att has int

What we do with the GUI: on the category definition page, add these attributes and create meta-information at run time and save it in the category table
On the other hand, when we define a product in a category, meta-information will be read and moved from the category table and filled in on the product definition page, as in other fields.

If you need more help, I can provide you with images so that you better understand how to do this.
we are experience and analysis, this is a very flexible approach

Answer 2

There is a table in which the names and types of fields are stored.

field_ID INT field_name VARCHAR field_type ENUM('int','float','text','richtext') 

There is a table that stores a link to a record in a table of records, a link to a record in a table of fields, and a field value.

 fieldvalue_fieldID INT fieldvalue_recordID INT fieldvalue_value BLOB 

Making it searchable is another problem - you will need to grab any searchable content from this fieldvalue value and the index that. This will be database specific. In MySQL, you can make this a TEXT value and add the MySQL FULLTEXT index to it.

Answer 3

Your best options:

• Allow the user to modify their own database schema, possibly by loading a module or by running a script.

• Use an XML field and a database that supports indexes and queries on the contents of this field

They are recommended by Martin Fowler, here: http://martinfowler.com/bliki/UserDefinedField.html