CODEWITHSUNDEEP
mysqldatabasedatabase-designrelational-database
brendosthoughts
brendosthoughts

Reputation: 1703

database design for user subscriptions

So I'm new to databases in the scope of the subject and looking for some advice for what I am sure is fairly simple. first I'm using MySql as my db I currently have two tables one for storing user accounts and details :

TABLE user
id | username | password | email_address | user_devices | contact_method

and another for storing video content by producers which looks like: 

TABLE series
id | series_title | still_broadcasting | last_updated |

I would like to implement a feature where Users can select series which they wish to be notified of when new releases are made available and also select how to be notified about these releases (email or push notification ) and how often to be notified (on arrival, hourly, daily, weekly ) I am wondering whats the best way to go about doing this?

I've thought of these ideas by myself but am looking for a second opinion/ better way altogether: (all ideas minus 4 involve storing how to notify user along with how often in user table)

  1. adding a text column to user table called following and just having csv's for each series
  2. adding multiple boolean column's to user table one for each series
  3. adding text column to series table with csv's of user's Id numbers following series
  4. creating an entirely new table for notifications though i don't really see the purpose of this as its very redundant

I then plan to just add cron jobs to my server to actually go about regulaurly sending notifications to user's

Thanks in advance for any help.

Upvotes: 0

Views: 4873

Answers (3)

Jesse
Jesse

Reputation: 418

If I were you I would add a third table as following:

TABLE user
id | username | password | email_address | user_devices | contact_method |notification_type

TABLE series
id | series_title | still_broadcasting | last_updated

TABLE followings
id | user_id | series_id

In notification_type I would put (on arrival, hourly, daily, or weekly), now in the followings tables I will store all the user's preferred series.

Doing this way makes easy to add, delete, update, or select all user's preferred series. All will be simple SQL queries. Also you avoid parsing comma separated strings.

for example, if you want to get all preferred series of an user:

SELECT * FROM followings AS f INNER JOIN series AS s ON f.series_id = s.id WHERE f.user_id = ?

if want to get all users that prefer a serie:

SELECT * FROM followings AS f INNER JOIN user AS u ON f.user_id = u.id WHERE f.series_id = ?

Upvotes: 1

No'am Newman
No'am Newman

Reputation: 6477

A partial answer which complements what has been written in other answers:

Don't keep a list of devices in the 'user_devices' field - break this out into a separate table. In fact, you'll need two tables: one to list the various devices, and one a join table which has two fields: user_id and device_id. This will enable you to track which user has which device, but also to provide a list of users per device.

Upvotes: 1

Simon at The Access Group
Simon at The Access Group

Reputation: 7027

First of all, it might be worth giving some articles on basic database design a read. A quick google turned up this which covers identifying relationships

http://www.datanamic.com/support/lt-dez005-introduction-db-modeling.html

Your best bet is to use a linking table i.e.

CREATE TABLE userHasSeries (
    userID INT,
    seriesID INT 
);

This can then be used in an INNER JOIN query to get the users choices. What you are doing here is an n:m link between 2 tables. An example inner join would be

SELECT
    u.id AS userID,
    u.username,
    s.seriesID,
    s.series_title,
    s.still_broadcasting,
    s.last_updated
FROM users AS u
INNER JOIN userHasSeries AS uhs
    ON uhs.userID = u.id
INNER JOIN series AS s
    ON s.id = uhs.seriesID

If users.user_devices is also a comma seperated list I would advise heavily that you adopt a similar n:m approach there also.

Upvotes: 1

Related Questions