Dedicated database per user vs Dedicated column for user

Question

I was creating a database with Androidx-Room. It contains 3-4 tables and stores some data belonging to the user. Now I was planning to add multi-user login in the same device. In this case, what is the best strategy? Should I create new database with same schema for each user (data belonging to a specific user can be identified with database name like 'myappname_$userId') or should I add a column to every table in the database to indicate that row belongs to which user (data belonging to a specific user can be identified by changing the existing query to add 'WHERE COL_USER_ID LIKE :userId').

I tried searching about this and found some similar questions in StackOverflow. But I was unable to draw any conclusions regarding this. The only point I got is that, if you use second approach, then there is no need to switch up databases when a user logs out and new user logs in. Apart from that, I was unable to collect any pros or cons for both approaches.

Answer 1

I'd suggest that a single database approach is more practical and more efficient overall.

With multiple databases you will:-

• have to have more complex code and cpu resource intensive code to switch databases as opposed to having an extra column and WHERE clauses. However, here's an example that uses multiple databases based upon template entities (tables) albeit just the single table in the example Can Android Room manage multiple databases and create databases from a template database?

  • The example revolves around a master database that stores the available databases catering for the dynamic allocation of new databases. The underlying table would probably be expanded to include columns to cater for user ownership and validation.

• use more storage space for the same amount of data as the data will be spread across some of which will not be entirely used, so at the minimum a greater amount of freespace. The more components (tables/indexes/view/triggers) the larger the impact.

  • rather than say an average free space of 2k per component so assuming 4 tables for 4 users that 32k could store (easily) 4000+ worth of extra column data.
    • this assumes worst case of 8 bytes per id to reference the user. However an id (INTEGER) can take up as little as 1 byte. BUT it does assume the use of efficient referencing (long as opposed to string).

• perhaps be more efficient extracting data as there is less data to extract and with reduced search requirements (i.e. no 'WHERE COL_USER_ID LIKE :userId'))