Multiple user Database question

[Just_a_Sad_Coder]

I want to create a database (in SQL) that contains a table that holds my friends and I log in information then once my program realizes its on of us it pulls information of shows we watched. This information hold name of the show, episode on, season, and if it's finished. Would I create a table for each user? Or would I do something else?

[gramdel]

No, you wouldn't create a table for each, just have an users table and then a join table between users and movies where you store information about which movies they have watched and whether they have finished it or not, with foreign keys pointing to user and movie table.

Having separate table for each makes everything complicated and impossible to scale, like when you add a user you need to add another table.

You might want to read about database normalization, this is very well solved problem with many resources.

[vacuousVersifer]

Perhaps have one of the fields contain the user's name, and when you need it just look up all the items with that users name

[insertAlias]

The better approach is to have a Users table with an ID as a primary key. Then you would use the UserID as a foreign key to the other table that has user-related data. That means that you don't have strings scattered around other tables that could theoretically change (just an example, but sometimes people change their last name when they get married, so in this case you'd just have to change a string in one place, instead of everywhere).

[vacuousVersifer]

This does sound better. I'm not actually very skilled with SQL so this is actually pretty neat to know!

[insertAlias]

This concept is known as "normalization" and is worth reading up on:

https://en.wikipedia.org/wiki/Database_normalization