What should be the structure of SQL queries, given the current user?

Question

SQL

There is an application on asp.net webforms with all sorts of GridView, DetailsView tables connected to MSSQL database. There was a need for several users, and for each user to see only their data in these tables. I can roughly imagine how this is done, but I would like to hear comments, all of a sudden I thought up something wrong for myself :)
1) Create a table of users.
2) Add UserID as a foreign key to the main table (for each record)
3) Change the query "Select * from 'table'" to "Select * from 'table' where UserID = @UserID" After that, take the parameter @UserID from# CurrentSession, which holds this ID after logging in to the site. And accordingly, the rest of the procedures should be redone in the same way for updates, dividers, etc. :)
Am I thinking in the right direction? Or are there better (correct) options?

Answer 1

You are thinking in the right direction. But first, I recommend reading more about relational databases and the principles of building tables / queries.

Answer 2

The direction of thought is correct, from a technical point of view too. I'm not sure about the need to update updates and delits - if you have already checked the UserId and found out that the entry belongs to a specific user, and received its Id - then it is enough to delete only by Id (except, of course, the case when you need to delete ALL entries specific user).
The correctness of this option depends on your task. If you just need to know the owner user, then everything is fine, but if you later want a more complex system for accessing records - for example, giving other users access to user A's records, then the database schema will also become more complicated.

Answer 3

The direction is correct.
Often databases grow, think carefully about IDs, start with the type of stored data and the full way to access an object in the database through a nested query...
I don't think it's necessary to mention the differences in character types char, varchar, text... and the advantages of varchar such as storage of a certain existing length and portability to other DBMS ...
But you don’t have to think about it, but when the time comes and the database will have 50k+ records, it will not be very pleasant for someone to work with it and it’s not a fact that it won’t be you.
In general, you understand me :)

Answer 4

Good afternoon! Perhaps it makes sense to take the MembershipAPI out of the box and not worry too much about the user model? And think, in principle, in the right direction. I will support Stanislav Makarov - if you do update and delete by record id, then you do not need to rewrite all these queries. Perhaps using an ORM will make your life easier.