CODEWITHSUNDEEP
mysqlhistoryprofiles
Michael Beeson
Michael Beeson

Reputation: 2875

MySQL: Where do I store each user's profile information in a website?

Sorry if this has been covered - I've been looking for hours but I think I simply lack the vocabulary to search effectively.

I'm trying to figure out how I should store profile information for each user. By profile information I don't mean information like email and the like, but more their preferences regarding the site I'm working on.

It's a language learning site, and I want users to be able to save their "progress", giving them the option to flag a lesson as learned.

I also want to keep track of which exercises they have done, so that I can try to only give them exercises they haven't done (or when they've used up the available exercises, start from the least recent). I'm just not sure where to store all this information.

Should I have a lookup table linking users to lessons? I fear this will get huge as the number of users and tables increases. Seeing as its just a boolean, I considered giving each user an int (and later more ints as an array) where each bit represents a lesson, and performing bitwise operators on those numbers to get the information about which lessons they've saved... though that sounds like it could be cumbersome in the future.

As for remembering which exercises they've done, I fear this will lead to a huge amount of waste if I try to save it in mysql. Could I try to have this done on the user's computer using cookies, and anybody who has cookies disabled will simply have to deal with repeating exercise questions?

Maybe I should think about other tables and even other databases! I don't know!

Sorry for all the rambling nonsense. At the very least I'd appreciate some pointers towards what I need to read up on...

Upvotes: 0

Views: 1509

Answers (2)

bcoughlan
bcoughlan

Reputation: 26627

You'll need a junction table to link each user to different exercises (many-to-many relationship):

user_id(int) exercise_id(int) learned(boolean)

You don't have to have entries for every possible combination, you can add each combination when a lesson is flagged as learned.

The bitwise method is going down a bad road, you'd need a bit for each lesson... it's not scalable.

Upvotes: 0

Chad Birch
Chad Birch

Reputation: 74528

A lookup table between the users and the exercises is the simplest and most flexible, and you really shouldn't have to worry about the size of it. It'll have a user id, an exercise id, and some sort of progress variable, so (depending on your needs) that's probably going to be less than 10 bytes of space per row. 1 million rows wouldn't even take up 10MB of space.

I'd probably just have records only get created in the table once the user has made some sort of progress on a particular exercise. So if you ever try to look up a user's progress on an exercise and a row isn't found, that means that they haven't done anything on that exercise. That way you only need to create rows to represent progress, and it should keep the number fairly low overall.

Upvotes: 3

Related Questions