What is best way to store custom user fields in ms sql for .net web application?

Question

I have a CRM application that has all commonly needed profiling fields that a user may need to store customers and related data. But, there are needs for additional related specialty data to be stored for each user's customers.

I have been putting this off as I am still perplexed on the pros/cons of the ways to store user supplemental data in the database.

IDEA 1: Framework of Permanent Application Tables

I have created a framework that requires 5 tables (at least) that would store all supplemental data for all users as a permanent store. It limits the user to adding expanded fields only to tables we designate. It does not allow user to create a new custom table.

User can:

Select a table from a list (Customer, Inventory) that he wants to add extra field.

Name the field and select the data type

New field is added to his form for entry/edit.

The framework knows the key field name (CustomerID, InventoryItemID) for that table and can join those fields into custom views

(Just to give you an idea why this scares me, here are the tables...)

UserDefinedObject - List of keys field names from our app tables that user may select to store supplemental data fields (stores Name of the parent field that the joining ID belongs to (E.g. CustomerID, InventoryItemID, etc.). This provides the JOIN ON field with app tables.

UserDefinedObject_RefObjectType - Reference table for UserDefinedObject: This is the table name that UserDefinedObject is key for (E.g CustomerID belong to Customer. This provides the JOIN table name.)

UserDefinedObjectAttribute - This is the user's choice field name they want to call the custom data field. (E.g FavoriteColor)

UserDefinedObjectAttribute_RefDataType - Refferece table for UserDefinedObjectAttribute : The data type the user want to store in the field. It is stored as string, but can be converted to user's type when needed.)

UserDefinedObjectAttributeValue - The actual value user want to store and the owner (key value)

E.g. * Custom defined field (Attribute) is FavoriteColor * FavoriteColor Value is Blue and belongs to ObjectType, Customer * Customer row is one with key value of CustomerID, 1212121.

Finally, value is stored as a string but can be converted to it the user-defined type on-read using the UserDefinedObjectAttribute_RefDataType.

PROS: Limited tables. Easy joins.

CONS: User can only add fields to predefined tables. That may be sufficient, but ?

IDEA 2 (I am leaning this way):

Allow the user (through a UI) to specified fields/types, table name that are parsed into an SQL script to create personal semi-permanent tables for each user's needs.

Additionally, and optionally, user could select the key value from app table that their newly created table should JOIN on. These tables would be "extensions" of an existing application table making joins pretty easy.

Unlike the framework this would allow user to select common tables like the framework above but would allow the creation of non-relational tables for what ever they need.

The user's tables would be deleted if the user no longer used the application.

I would appreciate some direction from real world experience.

Answer 1

I am not sure if I have understood correctly. But given the case that a user wants to store it's last time when he bought a book. What you need is a datastructure that stores this somehow in your database, am I right?

We have similar requirements I think. We use one table that stores following information: FieldName (string), FieldValue (string) and some other stuff. Then we store Metadata to all fields in our system. E.g. we create a field named LastBookBuyDate of type DateTime. The client then is able to render all fields based on it's type. When the user inputs some data, a new line is added to the table: (LastBookBuyDate, '2018-07-.....').

So we mix all datatypes together into one column of type string. That means, we have no indices at the value column and are not able to do complex queries with it. But until now there are no requirements to do so.

Btw I do not understand why to you want to join on tables that are unknown to you? As I understand you would create tables dynamically based on the user wishes. How could you then write "joins" onto those tables?