I'm trying to design a schema for a user having multiple accounts.
User ID 1 has Apps A and B
User ID 2 has App B only
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
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?