January 2, 2007 at 5:14 am
Hi,
I'm using SQL2k as the back end database to my CMS and want to provide users with the ability to store their own user defined fields for storing form data.
I already have the infrastructure in place to enable them to build the form, but now need to provide them with some back end tables to store the data in.
The web forms will be used for collecting user data that is stored in a standard table called SiteUsers (PK SiteUserID)- what I want to do is to provide a facility so that the client can create their own fields.
So far I am considering creating two additional tables - one called CustomFieldTypes providing details of the type of data to be stored and then a second join table called CustomFieldData linking the SiteUser to the CustomFieldType and storing the actual value, however am unsure of the best field structures for the tables.
This arrangement will primarily be used for storing Dates, Ints, NVarChar, NText and bit values and I will also want to store some validation information in the CustomFieldTypes table.
So what is the best route for structuring these tables?
Cheers,
Julian
January 2, 2007 at 9:14 am
I'd do it your way strictly for security. This way they're just adding "data".
Usually you'd do the metadata in CustomFieldTypes. Maybe an ID, a data type, a tablename or owner or something to idenitfy it.
Then in CustomFieldData, use a PK, an FK to CustomFieldTypes, the data, then maybe owner or form/field/some unqiue identifier if you could have multiple people adding this data.
January 2, 2007 at 9:32 am
OK, thanks for the re-assurance, however my remaining concern is over the best way to store the actual data.
Do I have a column for each field type in the CustomFieldData table (i.e. a field for int values, a bit for bit values, etc) so if it is supposed to be storing an int value, we check the int field, etc or do I just have a single field like an ntext that can store all the possible data and then I convert to the appropriate tyoe when I retrieve the data from the database?
Does that make sense?
Cheers,
Julian
January 2, 2007 at 10:23 am
Try searching the internet for "Entity Attribute Value". To get started, look at http://en.wikipedia.org/wiki/Entity-Attribute-Value_model
Some alternative:
Add a text column and use this column to store the values. The encoding of the components of this text field would be stored in a different table.
Use XML
SQL = Scarcely Qualifies as a Language
January 2, 2007 at 11:20 am
Hi Carl,
The Entity Attribute Value approach is what I want - interesting to see the number of articles totally slating it and then others raving about it!
Now to plough through the links to find the best way of doing it.
Cheers,
Julian
January 2, 2007 at 1:00 pm
Hello Julian,
I suppose I will be one of the ones to slam the "Entity Attribute Value" approach, but I do understand its value in this scenario.
Would you consider a limiting number of possible custom fields? You could create two tables, one "definition" table (that stores column label and data type) and a second table for the data itself consisting of sql_variant columns. This allows for foreign key joins against the standard table and still allows a great deal of flexibility.
Regards,
Michael Lato
January 2, 2007 at 4:20 pm
Yes I have been wondering on the best field type to store the actual data - I see some versions of the Entity Attribute Value approach use different tables for storing the different data types a bit like this:
http://www.research.ibm.com/journal/sj/461/eggeb1.gif
sql_variant will work in most cases unless I need to have an NTEXT field.
Time to make a descision.... 😉
Cheers,
Julian
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
