1

I have clients, each of whom have an app with a bunch of users.

Their user data could be pretty different, but there is also a lot of overlap. Ex: all their users have "gender" and "age" and plenty of other things, so it makes sense to have a standard user_table schema across all clients (with a column for each attribute).

But there is also user information specific to each client. Ex. one might have "relationship_status" for their users, which other clients do not have. While another has "height" for their users, which other clients do not have.

How to I design a schema that is appropriate for both of these cases at once?

One possibility is to somehow have a different schema for each client, but this seems like it could be unnecessary hassle. Another possibility is to put every column in the standard schema across all clients, but then the columns that are unique to a single client would just sit empty for all the other clients.

2 Answers 2

1

It depends on whether or not you need to do anything "interesting" with that additional, client-specific data.

In practice, it is unlikely that any processing will take place on custom fields, since you would have to provide that processing yourself, with some sort of plugin infrastructure or provide custom builds for each client, an arrangement which can quickly become unwieldy.

If there is no additional processing logic required, and custom fields can be provided to the client in a tabular format (as opposed to a columnar format), you can use an Entity-Attribute-Value design to provide custom field capability. This allows the client to add the field definitions himself.

Further Reading
Entity-Attribute-Value model

-3

In my honest opinion having different sets of tables for different sets of users will add complexity to your code by having 'X' amount of SQL queries. Because for one schema you'll have one SQL Query string for grabbing the data from there, but for another schema you'll have another SQL Query string from grabbing data from there.

Plus I think it makes it harder to maintain.

If this schema is supposed to be a general schema/table containing user data then I find it easier having one schema only and for the columns that don't apply to a specific user then just put a default N/A text.

This may provide a database table with more N/A's in it than you like. But I think that's a trade off well worth it by making sure you only have one set of SQL Queries for just that one schema.

1
  • 1
    While I agree multiple tables are a bad idea because of the explosion of queries, I strongly disagree that you should use some custom "N/A text"--this is really where null values make sense.
    – mgw854
    Mar 28, 2015 at 18:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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