How would you design this DB?

Question

Hi,

We are launching a website (paid subscription) and the sign up process includes entering an activation code. Activation codes are printed on scratch cards and sold via offline channels. Some of these cards are for 1 month access. Others are for 3 months and 1 year. Activation codes are unique 10-digit random numbers.

When the access expires, users can buy another activation card and extend the subscription by entering the new activation code. Additionally, we should also be able to extend their subscription if they request for it. For example, until a certain date (e.g. 1 additional week).

Considering the above information, how would you design the DB for the user-activation_code relationship? Do you think this design is good?

tbl_user
----------------
id
name
status_id

tbl_user_status
----------------
id
description

tbl_activation_code
----------------
activation_code
activation_code_type_id
activation_code_status_id
user_id
activated_date
expiry_date

tbl_activation_code_type
----------------
id
description

tbl_activation_code_status
----------------
id
description

Update: Activation codes will be required only:

1) Upon initial sign up

2) Closer to the access expiry date (say, 7 days) when the system displays a notification with a link to page to enter the activation code

3) After expiry, when a user tries to login, she will be asked for the activation code

Therefore, a user is not expected to key in the activation code as and when wanted.

Answer 1

I would consider a bit of denormalisation - at the moment, to determine whether a user currently has access or not you have to look through potentially multiple records for that user in tbl_activation_code to see if there is an active record for that user.

So it might be worth adding a surrogate IDENTITY/autonumber field in tbl_activation_code, and adding a foreign key to that in tbl_user - this would point to the user's current activation code record, simplifying the scenarios where you need to find the current state of a user's access. This way, a user record will always reference directly their current activation code, plus you still have the full history of their previous codes.

Answer 2

It's not bad. However, I would suggest that you add two fields to tbl_user:

tbl_user
----------------
id
name
status_id
activated_date
expiry_date

Of course, activated_date holds the date they were first activated, while expiry_date holds the date when they will expire. You also need a procedure to update this expiry_date, whenever they buy a new card. This procedure should handle two cards with overlapping dates, so the user doesn't double-up payment for a particular period. For example:

• Card 1 - Sep 1 to Sep 30
• Card 2 - Sep 16 to Oct 15

There are fifteen days of overlap there, so the user's activated_date should be Sep 1, while their expiry_date should be Oct 30 (Oct 15 + 15 days).

Considering this, I would change tbl_activation_code, as expiry_date becomes a bit misleading. Instead, create a column called access_days that will be used to calculate the user's expiry_date.

Also, if you want to remember cards that were issued, even if not activated, then I would split tbl_activation_code into two tables:

tbl_activation_code
----------------
activation_code
activation_code_type_id
activation_code_status_id
access_days

tbl_activation
----------------
activation_code_id
user_id
activated_date