Optimize table structure

Question

Have 2 millions users. Every user can be subscribed to 3 services. In this case want to know which structure will be better.

1. Store one row in table for one user, and have more fields for every service
2. Store three rows for every service, and will have in this case less fields, but more data

Answer 1

The table structure depends on the types of queries that you will be doing, i.e. how will you select the data, whether you need indexed selects. If you anticipate that each mailing list will have at least 10% of the users subscribed, then you may not get big gain from indexing, and you can make just one integer column, when each mailing list will have one own bit (2^N), e.g.

SELECT * FROM Subscriptions WHERE (MailingListFlags AND 8) <> 0;

Instead of 4 put 2 or 1 depending on the bit you need, where 1 = first bit, 2 = second bit, 4 = third bit, 8 = fourth bit, etc.

This logical AND operation will not allow you to use indexing effectively. But if you wish to select mailing lists one by one, you may only get gain from indexing when the number of results will be low, for example less than 10% from the total number of users. Then make boolean columns for each mailing list. But this structure will not be very flexible. Should you need to create new mailing lists, you will need to add new columns. A more flexible approach is to denote an integer ID for each user (other user data like name and email will be stored in a different table) and for each mailing list. So you will just have two integer columns, but the user ID column will not be unique, e.g. if user 1 is subscribed to lists 2 and 3 and user 2 is subscribed to lists 1, 3 and 4, the table will look like that

User | List
===========
   1 |    2
   1 |    3
   2 |    1
   2 |    3
   2 |    4