18

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

  • 3
    If different companies have different, multi-valued data sets on each customer, then you absolutely need a COMPANY_CUSTOMER linking table. Everything else will cause you great pain very soon. – Kilian Foth Mar 11 '15 at 15:37
  • How would a linking table help with the custom data? the columns will still have to be different – noobcser Mar 11 '15 at 15:49
  • 1
    You must represent the fact "Kilian's password for IKEA is 'kitten'" with a tuple such as "COMPANY:IKEA,CUSTOMER:Kilian,ATTRIBUTE:password,VALUE:kitten". Anything simpler won't do the job. – Kilian Foth Mar 11 '15 at 15:57
  • 3
    A schema is a fixed thing, by definition; you can't set one up if you don't know what the fields that you need are. Have a look at Entity-Attribute-Value for one way problems like this tend to get solved in a relational database. – Mason Wheeler Mar 11 '15 at 16:06
1

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.

  • The regular expression [^\w-]+ should pretty well do it, not allowing anything that's not 0-9A-Za-z_---but yes, sanitizing is a must here to protect against maliciousness or stupidity. – Regular Joe Oct 20 '17 at 14:13
-1

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.

6

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.

  • this is what I am thinking about or maybe JSON columns. How is performance on querying, filtering reporting compared to solution proposed by PRoe. – kos Jul 7 '18 at 14:48
  • 1
    Anytime you compress data into json or xml and then toss it into a column it will be terribly slow to search on. If you need to search the data presented in my answer above then I would advise using indexed views to retrieve the data. If that solution isn't ideal then I would recommend using ETL to copy the data into a structure that can be easily searched and reported on. – P. Roe Jul 9 '18 at 20:16
  • In the above approach , how do we implement the search thing , as diff. companies want to search on their fields , including fields of users as well. What is the correct approach to provide scalable searching on top of this – techagrammer Aug 14 '18 at 8:56
  • In nosql databases, you may have redundant data, but it is structured in a way to be searchable. The one showed above is by unique identifier. Another one could may be \Company\Name. It’s similar to having multiple indexes. – JeffO Aug 21 '18 at 9:51
0

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.

  • This can work as well, but I feel becomes limited once you actually need to do something against the data stored in the XML / JSON field. – Andy Mar 11 '15 at 16:32
  • @Andy - True, there is a another layer. Query DB and parse XML as opposed to just query DB. I don't know if I would call it limiting, just more cumbersome. But, it would would something to consider if the custom attributes were used extensively. – Jon Raynor Mar 11 '15 at 17:09
13

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.

  • thanks - I though about such approach - please see edit. 2 problems: 1) The data grows as rows, which mean to get a full picture of a user, you'll have to do a lot of joins. 2) "value" will always be stored as VARCHAR to be generic, even if the value is actually int or boolean etc – noobcser Mar 11 '15 at 19:42
  • 1
    If you use int/bigint for the table identities and join on those you wont have any performance problems until you're at an extreme number of rows. Now if you start to search based on the attribute values this could present a problem if you start to get a huge number of records. In this case I'd work with a DBA to determine if there are indexes that could be created or perhaps an indexed view that could speed up these kinds of searches. I've used a similar schema and it takes in 100 million records a year with no performance issues whatsoever so the base design works pretty well IMO – P. Roe Mar 12 '15 at 16:22
  • If reporting, filtering, querying is needed and different attributes may belong to different data sets. Would this approach be better than NoSQL? I am trying to understand performance difference. Similar situation only user can define reports that contain user defined fields. – kos Jul 7 '18 at 14:46
  • In the above approach , how do we implement the search thing , as diff. companies want to search on their fields , including fields of users as well. What is the correct approach to provide scalable searching on top of this – techagrammer Aug 14 '18 at 8:56
  • You can search it normally with a lot of joins. You can use an ETL script to extract the data you want to search and place it in a more denormalized structure. Lastly you can attempt to utilize indexed views as a method to search. Personally I recommend the ETL method to generate denormalized structures that are easy to search. – P. Roe Aug 16 '18 at 19:10
-1

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.

  • 3
    And if you have thousands of customers a table per each can quickly become unmaintainable, not to mention that you'll need custom code for each customers' custom fields. – Andy Mar 11 '15 at 16:25
  • @Andy: Guess what? The situation will be even more unmaintainable if you mix a thousand different schemes into a single table ! And yes, you probably do need custom code for custom fields. Again that is simpler, not harder, if each customer has a clean, separate table. Trying to pick company X's fields from a thousand others is a bloody mess. – MSalters Mar 11 '15 at 20:49
  • Are you referring to my answer or the OPs idea of tacking all the extra columns onto the customer table? – Andy Mar 11 '15 at 21:01
  • 2
    The goal here is to find a maintainable & scalable solution. Creating a table per customer is definitely the opposite of that. Everytime you on-board a new customer, it's not realistic to: run a create table script, update your code (Entity objects), and re-deploy. – tsOverflow Mar 13 '15 at 14:21
  • This whole idea of using shared tables for all customers is itself a separate SaaS architecture discussion, and there are some good reasons to keep customers in different tables (or even in different databases, allowing per-customer backup/restore and scaling out). In this scenario, creating cusotm columns in the main table is a no-brainer. I upvoted, and I wonder why people downvote this just because they don't like this approach. The inner platform effect is a reality: by using an EVA model your querying will be harder, saving harder, integrity harder, etc. – drizin Aug 4 '18 at 18:28
3

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).

  • thanks - I though about such approach - please see edit. 2 problems: 1) The data grows as rows, which mean to get a full picture of a user, you'll have to do a lot of joins. 2) "value" will always be stored as VARCHAR to be generic, even if the value is actually int or boolean etc – noobcser Mar 11 '15 at 19:42
  • 1
    @noobcser The data growing as rows doesn't really matter, after all databases are designing around rows and joins. In any event you'd more likely use Common Table Expressions for this which are quite good at this sort of thing. I'm not sure if you missed the part where I said you can use sql_variant as the data type for the value column, which stores the value as whatever type you stick into it. While i'm naming MS SQL server feature names, i'd expect other mature DBMS to have similar features. – Andy Mar 11 '15 at 21:14
  • 1
    @noobcser FYI I've actually encountered these requirements quite frequently in my career and have experience with each of the proposed solutions, so I'm suggesting the one which has worked best in my experience. Use of xml data types for this sort of thing is partially why I hate that MS adding xml as a native data type. – Andy Mar 11 '15 at 21:21
-1

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.

protected by gnat Jul 31 '17 at 5:08

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.