CODEWITHSUNDEEP
sqldatabase-design
user1601983
user1601983

Reputation: 15

Simple database table design

I'm trying to design a database structure using best practice but I can't get my head around something which I'm sure is fundamental. The DB is for the users(100+) to subscribe to which magazines(100+) they read.

I have a table for the usernames, user info and magazine titles, but I'm unsure where to list the magazines that each user follows. Do I add a column in the user table and link it to the magazine table or would each user be setup with their own "follow" table that lists the magazine there? I'm getting myself confused I think so any help would be great.

Regards

Ryan

Upvotes: 1

Views: 335

Answers (6)

Prescott
Prescott

Reputation: 7412

A User_To_Magazine table, that has two columns - UserId and MagazineId, and the key is composite containing both columns

Upvotes: 0

Manish Prajapati
Manish Prajapati

Reputation: 585

You should create a separate table called UserMagazineSubs. Make the UserID + MagazineTile ID as a composite key.

This table will capture all User and Magazine relationship details.

Upvotes: 0

Baqir
Baqir

Reputation: 24

First of all, you must understand a many-to-many relationship, like take your example of users and magazines. First understand the scenario : A single user can follow many magazines, and a single magazine can be followed by many users, so there exists a many-to-many relationship between users and magazines.

Whenever there exists many-to-many relationship between two entities, we have to introduce a third entity between them which is called an associative entity!

so you have to introduce a third entity named as per your choice and it will be containing information about which user is following which magazine

you can go to http://sqlrelationship.com/many-to-many-relationship/ for better understanding using diagrams

Upvotes: 0

cclark
cclark

Reputation: 1412

You definitely do NOT want to add a column to the user table and have it refer to the magazine table. Users would only be able to follow or subscribe to one magazine which doesn't reflect the real world.

You'll want to have a join table that has a userId and a magazineId. For each magazine that a user subscribes to there will be one entry in the join table.

I'm inferring a little bit about your table structure but if you had:

User (id, login)
Magazine (id, name)
User_Magazine (userId, magazineId)

Perhaps this last table should be called subscription because there may be other information like the subscription end date which you'd want to track and that is really what it is representing in the real world.

You'd be able to put an entry into the User_Magazine table for every subscription.

And if you wanted to see all the magazines a user with the login jdoe had you'd do:

SELECT name 
  FROM User, Magazine, User_Magazine 
  WHERE login = 'jdoe'
    AND User.id = User_Magazine.userId
    AND Magazine.id = User_Magazine.magazineId

Upvotes: 0

David Robertson
David Robertson

Reputation: 1829

You should have a users table, with an auto-incrementing primary key, username, and anything else you want to store about that user.

Next, a magazines table which contains another auto-incrementing primary key, the name of the mag and anything else you need to store about that magazine.

Finally, a subscriptions table. this should have an auto-incrementing primary key (actually that's not really necessary on this table but personally I would add it), a user_ID column and a magazine_ID column.

To add a subscription, just add a new record to the subscription table containing the ID of the user and the ID of the relevant magazine. This allows for users to subscribe to multiple magazines.

If you want to get fancy you can add referential integrity constraints to the subscriptions table - this tells the database management system that a particular column is a reference to another table, and can specify what to do upon modifying it (for example you could have the DBMS automatically delete subscriptions owned by a particular user if that user is deleted)

Upvotes: 0

Elliot B.
Elliot B.

Reputation: 17651

What you're struggling with is called a many-to-many relationship.

To solve this problem, you need a third table--perhaps called user_magazines. This third table should two key fields, one from the user table and the other from the magazine table. For example, user_id column and a magazine_id column. This is called a compound key. With both of these columns, you are now able to discern which books have been read by a whichever user.

This is best understood visually:

enter image description here

In the picture above you can see that the third table (the middle table, stock_category) enables us to know what stock item belongs to which categories.

Upvotes: 4

Related Questions