I have to allow customers to include new fields to some record, e.g. if there's a message record, a person might want to give a "SSN" number area along with a "Birthdate" date/calendar area. They'd do that with the UI obviously.
Individuals fields should then be accessible for entry for those contact records.
Thinking about that my application runs for a lot of customers at the same time (not really a single-company deployment etc.) and theoretically everybody could add their very own custom fields, what will be the best practice to keep these details inside a database, particularly when it must be searchable?
Possess a table that stores the area names and kinds.
field_ID INT field_name VARCHAR field_type ENUM('int','float','text','richtext')
Possess a table that stores a hyperlink for an entry within the record table, a hyperlink for an entry within the area table, and also the area value.
fieldvalue_fieldID INT fieldvalue_recordID INT fieldvalue_value BLOB
Which makes it searchable is yet another challenge - you should grab any searchable content from that fieldvalue_value and index that. This is database-specific. In MySQL you can make that the TEXT value and give a MySQL FULLTEXT index onto it.
We add almost within our all application/items additional attribute/area support for given versatility to user
Like there exists a product category, Within the category, customer can define additional attribute associated with a product
what we should do within the DB level is:
Category Table possess some additional column like: Text1Att, Text2Att...for text value support, Num1Att, Num2Att... for Number value support, Date1Att, Date2Att... for datetime value support, ID1Att, ID2Att... support for ID using their company table like you can include dropdown, listbox,...
here all of the column have String datatype.
what we should store here's
we'll store meta information here, like for Text1Att meta is
Caption of fieldControl TypeMax lengthis Needed fieldis Custom validation needed Custom Validation message
birth placetextbox100truetrueInvalid Value
Same for Number area ...
for date meta information may be like
birth dateCalendar controltruetrueInvalid Date
Caption of area Calendar control or could be otheris requiredis Custom Validation Custom Validation message
What exactly are doing in product table is add same quantity of column and also have datatype text1Att,.. is varchar, num1Att have number, date1Att have datetime, ID1Att have int
What we should do GUI side is : In category definition page add these attribute and make meta information at runtime and store in category table
However whenever we define product in category, meta information is going to be read and traverse from category table and populate in product definition page like other fields.
if u need further help, I'm able to supply you images to ensure that you'll better know how can be achieved this.
we're experience and evaluate, this really is much flexible approach