Schema for 1 User to 0..N Apps?

Question

I'm trying to design a schema for a user having multiple accounts.

Example:

• User ID 1 has Apps A and B

• User ID 2 has App B only

More App's will be created, i.e. C, D, etc., in the future.

My first thought was to, then, create a schema of:

create table account (
  primary key user_id, 
  string app_a, 
  string app_b
);

However, what I don't like about this approach is that my app_* columns will have NULL marks.

So, then, I thought it'd be worthwhile to do:

create table users ( 
    primary key user_id
);
-- note: I'm not sure if it's necessary to include the 'primary key id'
create table app_a (
    primary key id, 
    foreign key primary key user_id
);

create table app_b (
    primary key id, 
    foreign key primary key user_id
);

What I like above approach is that I'm no longer dealing with NULLable columns. In addition, to create a new app, I simply need to create a new table; rather than adding another NULLable column.

Yet, it seems that, in the second approach, I'm simply treating the tables user, app_a and app_b as lists.

What's the best approach (including any I've not mentioned) to capture a relationship from 1 user to 0 to N apps?

Answer 1

If the ultimate number of apps possible is very small (4-5 would be the limit for me, I think), what you've got can work.

If, at some point, there will be other data to store for each user and each app, and that data would vary considerably from one app to another, then it may be the easiest structure to work with.

If there will never be a need to store additional data per user per app; or, if that data would be basically the same from one app to another, then you could have an app table to store unique information about the apps (not tied to individual users), and have a user_app table to link users to their apps, and store any common data that would apply to all apps (first sign-in, last sign-in, whatever you might need, if anything).

CREATE TABLE app (app_id int PRIMARY KEY, app_name varchar(128), ...);
CREATE TABLE user_app
     ( user_app_id int PRIMARY KEY
      ,app_id -- foreign key to table app
      ,user_id -- foreign key to table user
      ... - other columns, if needed
     );

This is probably the best solution if the number of apps grows large (say, if you're tracking users and their apps in Google Play, rather than users and 4 in-house apps, where you're not expecting more in the foreseeable future).

Update - as noted by ypercubeᵀᴹ, user_app should have a unique index on (user_id, app_id), assuming each user can only have one account per app.

If it was possible for a single user to have multiple accounts on a given application for some reason, then you would want to include the account_id as a column. Then, you would not want the (user_id, app_id) unique index; instead, you would want a unique index on (app_id, account_id) (assuming that duplicate account IDs are fine across different applications; otherwise, the unique index should just be on account_id).