We help IT Professionals succeed at work.
Get Started

MySQL Database design and query help

hbiz
hbiz asked
on
507 Views
Last Modified: 2021-04-21
I have the following tables:

users (id, first_name, last_name)
category (id, name)
rank(id, user_id, rank)

Each user can belong to several categories.  And all users are in the rank table and have a value between 0.0 and 1.0, where 0 is the lowest rank and 1 is the highest. I’d like to setup additional tables to create the following webpage:

A visitor to the page (identified by either one of the recorded ids in the user table, or a numeric representation of their ip address) chooses a category and is presented with two randomly chosen users from the users table such that:

1) the visiting user_id has not seen this pairing in a period of 24 hours
2) the two users belong to the chosen category
3) the two users are within 1 rank value of each other.  Let me explain that last criteria - if all the ranks were sorted, the two chosen users would have adjacent ranks.

This is a hard one and I can’t for the life of me figure it out how to design my tables and query to answer this efficiently

I truly appreciate any help on this front.

Thanks
Comment
Watch Question
Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE