CODEWITHSUNDEEP
sqldatabasedesign-patterns
jack
jack

Reputation: 147

SQL database design pattern for user favorites?

Asked this on the database site but it seems to be really slow moving. So I'm new to SQL and databases in general, the only thing I have worked on with an SQL database used one to many relationships. I want to know the easiest way to go about implementing a "favorites" mechanism for users in my DB-similar to what loads of sites like Youtube, etc, offer. Users are of course unique, so one user can have many favorites, but one item can also be favorited by many users. Is this considered a many to many relationship? What is the typical design pattern for doing this? Many to many relationships look like a headache(I'm using SQLAlchemy so my tables are interacted with like objects) but this seems to be a fairly common feature on sites so I was wondering what is the most straightforward and easy way to go about it. Thanks

Upvotes: 3

Views: 7573

Answers (2)

gfish3000
gfish3000

Reputation: 1567

Yes, this is a classic many-to-many relationship. Usually, the way to deal with it is to create a link table, so in say, T-SQL you'd have...

create table user
(
    user_id  int identity primary key,
    -- other user columns
)

create table item
(
    item_id  int identity primary key,
    -- other item columns
)

create table userfavoriteitem
(
    user_id  int foreign key references user(user_id),
    item_id  int foreign key references item(item_id),
    -- other information about favoriting you want to capture
)

To see who favorited what, all you need to do is run a query on the userfavoriteitem table which would now be a data mine of all sorts of useful stats about what items are popular and who liked them.

select ufi.item_id,
  from userfavoriteitem ufi
 where ufi.user_id = [id]

Or you can even get the most popular items on your site using the query below, though if you have a lot of users this will get slow and the results should be saved in a special table updated on by a schedules job on the backend every so often...

select top 10 ufi.item_id, count(ufi.item_id),
  from userfavoriteitem ufi
 where ufi.item_id = [id]
 GROUP BY ufi.item_id

Upvotes: 6

simon at rcl
simon at rcl

Reputation: 7344

I've never seen any explicitly-for-database design patterns (except a couple of trivial misuses of the phrase 'design pattern' when it became fashionable some years ago).

M:M relationships are OK: use a link table (aka association table etc etc). Your example of a User and Favourite sounds like M:M indeed.

create table LinkTable
(
   Id int IDENTITY(1, 1), -- PK of this table
   IdOfTable1 int, -- PK of table 1
   IdOfTable2 int  -- PK of table 2
)

...and create a UNIQUE index on (IdOfTable1, IdOfTable2). Or do away with the Id column and make the PF on (IdOfTable1, IdOfTable2) instead.

Upvotes: 1

Related Questions