Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 6 down vote accepted

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.)

share|improve this answer
    
I see, im using Entity Framework (.NET) but doesnt matter. The 5 tables was an example. My app have 2 tables. Notes and categories. But I can query both separately. I want to see my notes but when editing categories, I want to see my categories alone so its seems I need the owner thing in both tables. Thanks. –  Jesus Rodriguez Aug 10 '11 at 12:16
2  
So in your design, could I have a category called "baseball" and you could have a different category also called "baseball"? If so, you should consider storing the categories as a global list and then a relation table to show which of those categories I like. This way, if you have a lot of users who like baseball, you only store "baseball" once. Seems trivial but as the category names get longer... –  Aaron Bertrand Aug 10 '11 at 12:27
    
Yup, but is not viable in my design IMHO. Is note taking app. I can have a category called "Girlfriend" but I can also have one called "WorkThings". I mean, there is infinite possibilities of category names, and not just name, every category have its options (like colors and so on), every user wants his own colors. But yeah, if it grows too much, will be kinda slow. –  Jesus Rodriguez Aug 10 '11 at 13:17
    
Still seems like color and other properties are attributes of the relation, not of the category itself. So you could still store the user's color choice in a table that has the UserID and the CategoryID. If there are infinite choices for category names, you can bet that you will have a lot of redundancy. –  Aaron Bertrand Aug 10 '11 at 13:30
    
Yep, but infinite choices of color too. Anyway, the questions is correctly answered :) Thanks. –  Jesus Rodriguez Aug 10 '11 at 14:25
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.