How would you design a user database with custom fields

This question is around how should I design a database, it can be relational / nosql databases, depending on what will be the better solution

Given a requirement where you’ll need to create a system that will involve a database to track “Company” and “User”. A single user always only belong to one company

A user can only belong to one company
A company can have many users

The design for “Company” table is quite straightforward.
Company will have the following attributes / columns: (let’s keep it simple)

ID, COMPANY_NAME, CREATED_ON

First scenario

Simple & straight forward, users all have the same attribute, so this can be easily done in relational style, user table:

ID, COMPANY_ID, FIRST_NAME, LAST_NAME, EMAIL, CREATED_ON

Second scenario

What happen if different companies want to store different profile attribute for their user. Each company will have a defined set of attributes that would apply to all users of that company.

For example:

Company A wants to store: LIKE_MOVIE (boolean), LIKE_MUSIC (boolean)
Company B wants to store: FAV_CUISINE (String)
Company C wants to store: OWN_DOG (boolean), DOG_COUNT (int)

Approach 1

the brute force way is to have a single schema for the user and let them have nulls when they dont belong to the company:

ID, COMPANY_ID, FIRST_NAME, LAST_NAME, EMAIL, LIKE_MOVIE, LIKE_MUSIC, FAV_CUISINE, OWN_DOG, DOG_COUNT, CREATED_ON

Which is kinda nasty because you will end up with a lot of NULLS and user rows that have columns that are irrelevant to them (ie. all users belonging to Company A has NULL values for FAV_CUISINE, OWN_DOG, DOG_COUNT)

Approach 2

a second approach, is to have “free form field”:

ID, COMPANY_ID, FIRST_NAME, LAST_NAME, EMAIL, CUSTOM_1, CUSTOM_2, CUSTOM_3, CREATED_ON

Which would be nasty on its own since you have no idea what custom fields are, the data type will not be reflective of the values stored (eg. we’ll store int value as VARCHAR).

Approach 3

I have looked into PostgreSQL JSON field, in which case you will have:

ID, COMPANY_ID, FIRST_NAME, LAST_NAME, EMAIL, CUSTOM_PROFILE_JSON, CREATED_ON

In this case, how would you be able to apply different schemas to a user?
A user with Company A will have schema that looks like

 {"LIKE_MOVIE":"boolean", "LIKE_MUSIC": "boolean"}

While a user with Company C will have a different schema:

 {"OWN_DOG ":"boolean", "DOG_COUNT": "int"}

How should I solve this issue? How can I design database properly to allow for this flexible schema for a single “object” (User) based on the relationship they have (Company)?

relational solution? nosql solution?

Edit: I’ve also thought about a “CUSTOM_PROFILE” table which will essentially store user attributes in rows rather than columns.

There are 2 problems with this approach:

1) The data grows per user grow as rows rather than columns – and this mean to get a full picture of the user, a lot of joins need to be done, multiple joins to the “custom profile” table on the different custom attributes

2) The data value is always stored as VARCHAR to be generic, even if we know the data is supposed to be integer or boolean etc

Please consider this as an alternative. The previous two examples will both require that you make changes to the schema as the application’s scope grows in addition the “custom_column” solution is difficult to extend and maintain. Eventually you’ll end up with Custom_510 and then just imagine how awful this table will be to work with.

First let’s use your Companies schema.

[Companies] ComnpanyId, COMPANY_NAME, CREATED_ON

Next we’ll also use your Users schema for top level required attributes that will be used/shared by all companies.

[Users] UserId, COMPANY_ID, FIRST_NAME, LAST_NAME, EMAIL, CREATED_ON

Next we build a table where we will define our dynamic attributes that are specific to each companies custom user attributes. So here an example value of the Attribute column would be “LikeMusic”:

[UserAttributeDefinition] UserAttributeDefinitionId, CompanyId, Attribute

Next we define a UserAttributes table that will hold user attribute values

[UserAttributes] UserAttributeDefinitionId, UserId, Value

This can be modified in many ways to be better for performance. You can use multiple tables for UserAttributes making each one specific to the data type being stored in Value or just leave it as a VarChar and work with it as a keyvalue store.

You also may want to move CompanyId off of the UserAttributeDefiniton table and into a cross reference table for future proofing.

Use a NoSQL database. There would be company and user documents. The users would have part of their schema dynamically created based on a user template (text to indicate fields/types for that company.

Company<uniqueidentifier>
    - Name: <Name>
    - CreatedOn: <datetime>
    - UserTemplate: <Text>

User<uniqueidentifier>
    - COMPANY_ID: <ID>
    - FIRST_NAME: <Text>
    - LAST_NAME: <Text>
    - EMAIL: <Text>
    - CREATED_ON: <datetime>
    - * Dynamically created fields per company

This is how it might look in something like Firebase.com You would have to learn how to do it in whatever one you choose.

If you’re frequently going to run into custom field requests, I’d actually model it pretty similarly to the database. Create a table that holds the metadata about each custom field, CompanyCustomField (who it belongs to, the data type, etc.) and another table CompanyCustomFieldValues which contains the CustomerId, FieldId and the value. If you’re using something like Microsoft Sql Server, I’d have the value column be a sql_variant datatype.

Of course this is not easy as you’ll need an interface that lets admins define custom fields for each customer, and another interface that actually uses this metadata to build a UI to collect the field values. And if you have other requirements, such as grouping of fields together or the need to do a pick list kind of field you’ll need to accomdate that with more metadata / other tables (e.g., CompanyCustomFieldPickListOptions).

This is non trivial, but it has the advantage of not requiring database changes / code changes for every new custom field. Any other features of custom fields will need to be coded as well (for example, if you want to regex validate a string value, or only allow dates between certain ranges, or if you need to enable one custom field based on another custom field value).

For one reason or another, databases are the one field in which the inner platform effect shows up most often. This is just another case of the anti-pattern popping up.

In this case, you’re trying to fight the natural and correct solution. Company A’s users are not company B’s users, and they should have their own tables for their own fields.

Your database vendor does not charge you by the table, and you don’t need twice the diskspace for twice the tables (in fact, having two tables is more efficient because you don’t store A’s attributes for B’s users. Even storing just NULLs takes space).

Of course, if there are sufficient common fields, you can factor those out into a shared Users table, and have a foreign key in each of the company-specific user tables. This is so simple a structure that no database query optimizer struggles with it. Any necessary JOIN is trivial.

An alternative to the other answers is to have a table called profile_attrib, or similar that the schema is completely managed by your application.

As custom attributes are added, you ALTER TABLE profile_attrib ADD COLUMN like_movie TINYINT(1), you could prohibit deleting them. This would minimise your join, while still providing flexibility.

I guess the bit trade-off is the application now needs alter table privileges to the database, and you have to be clever about sanitizing the column names.

Your question has many potential solutions. One solution is to store the additional attribrutes as XML. The XML can be stored as text or if your using a database that supports XML types as XML (SQL Server). Storing as text limits your querying ability (like searching on a custom attribute), but if storing and retrieval is all your need then its a good solution. If one needs to query, then storing the XML as an XML type would be a better option (although this is more vendor specific).

This will give one the ability to store any number of attributes to a customer with just adding an addition column on the customer table. One could store the attributes as a hashset or dictionary, one will lose type safety since everything will be a string to start with, but if one enforces standard format string for dates, numbers, booleans it will work out OK.

For more information:

https://msdn.microsoft.com/en-us/library/hh403385.aspx

@WalterMitty’s answer is valid as well, although if one has a lot of customers with different attributes one could end up with many tables if following the inheritance model. It depends on how many custom attributes are shared amongst customers.

You should normalize your database such that you have 3 different tables for each different type of company profile. Using your example, you would have tables with columns:

USER_ID, LIKE_MOVIE, LIKE_MUSIC

USER_ID, FAVORITE_CUISINE

USER_ID, OWN_DOG, DOG_COUNT

This approach assumes that you will know the shape of information a company wants to store before hand and that it will not change often. If the shape of the data is unknown at design time, it would probably be better to go with that JSON field or a nosql database.

My solution assume that you would be calling this query from a program and you should be able to perform post processing. You can have following columns:

ID, COMPANY_ID, FIRST_NAME, LAST_NAME, EMAIL, CUSTOM_VALUES

CUSTOM_VALUES will be of type string storing key and values pair. key will be column name and value will be column value e.g.

LIKE_MOVIE;yes;LIKE_MUSIC;no;FAV_CUISINE;rice

in this CUSTOM_VALUES you will save only those information which exist.
When you query from program you can split this string and use it.

I’ve been using this logic and it works fine, its just that you will have to apply filtering logic in code and not in query.

Filed under: softwareengineering - @ 17:47

Thẻ: database, database-design, nosql, sql

Thiết kế website giá rẻ

Danh mục

How would you design a user database with custom fields