CODEWITHSUNDEEP
mysqldatabase-design
Ben Harvey
Ben Harvey

Reputation: 1843

What is a good mySQL database design for multiple rankings?

I am trying to figure out the best method to design a database that allows users to rank a number of items.

The items are ranked by all users.

Every item will have a rank assigned to it automatically in the beginning. So when I create a user, the rankings table will be auto populated.

So something like this:

users (id, name) - 100,000+ entries
items (id, name) - never more than 1,000 entries

The only thing I can currently think of to house the rankings is this:

rankings (id, user_id, item_id, ranking)

But that feels wrong because I'll have 100 million entries in the rankings table. I don't know if that's ok? What other option could I use?

Upvotes: 0

Views: 397

Answers (1)

O. Jones
O. Jones

Reputation: 108776

Can each user assign either zero or one ranking to each item? Or can she assign more than one ranking to a given item?

If it's zero-or-one, your ranking table should have these columns

 user_id   INT   
 item_id   INT
 ranking   INT

The primary key should be the composite key (user_id, item_id). This will disallow multiple rankings for the same item for the same user, and will be decently efficient. Putting a separate id on this table is not the right thing to do.

For the sake of query efficiency I suggest you also create the covering indexes (user_id, item_id,ranking) and (item_id, user_id, ranking).

If you can get your hundreds of thousands of users to rank all 1000 items, that will be great. That's a problem any web app developer would love to have.

This table's rows are reasonably small, and with the indexes I mentioned it should perform decently well both at a smaller scale and as you add users.

Upvotes: 1

Related Questions