Where to save user data?

Question

I hope this is the right stackexchange place to ask.

Im really new using databases and I have a little question: Where to save the user data? I mean, if Im logged as "Foxandxss" on my app, and I want the list of my... questions... How it knows the questions that are mine?

I ended with a "Owner" column in the table with the data. I filter the rows comparing Owner with the current logged user. For one table maybe is OK but if I have like 5, is quite overkilling.

So, how is the typical database that manage user data?

Thank you.

Answer 1

You'll need to describe the other 5 tables. How is the data related? You should only ever have to filter for owner once if the data is already naturally related. For example, if I want all the questions that are mine, and their related answers, I should be able to do something like this (please ignore syntax, I have no idea what platform you're using):

SELECT Q.QuestionID, Q.Text, ..., A.AnswerID, a.Text, ... 
  FROM Questions AS Q
  INNER JOIN Answers AS A
  ON Q.QuestionID = A.QuestionID
  WHERE Q.Owner = 'Foxandxss';

The point: You should only need to check in one place for the owner. If you're storing the owner in the Answers table too, and for any given QuestionID that value is the same, this is wasteful and counter-productive.

(You probably wouldn't actually repeat the question data every time, since there may be 500 answers, and you would ideally have the username mapped to a userID of some kind so that you're not repeating that wide data throughout your indexes and data. But hopefully you get the drift.)