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 am developing a social network site and expecting very good response from people so expecting huge users (maybe more than million in years). My website is holding user information table and all its related information like

Location

  • Country
  • State
  • City
  • Location

Education

  • School
  • College
  • University
  • etc

So finally user table will join with these and will fetch information through views. I am maintaining index properly. However thinking for future and want to make best solution for Select query.

My question is what is best process for faster select query, shall I do inner join among thees tables or should I store direct location type information in user table as these changes very rare. All location fields will be nvarchar type and length around 100-250 char.

share|improve this question
2  
+1 for the optimism –  Lamak Dec 27 '12 at 15:11
    
What are you going to be doing with the data? Searching? Displaying the information for single users? How frequently (relatively) for each? This question is completely unanswerable without that information. –  Jon Seigel Dec 28 '12 at 2:59

2 Answers 2

I believe you are going to want to have things split out into multiple tables as you've described. Everything in the user table, and don't name it user lest you want to spend your application's lifetime writing [user], is going to result in every access to the database to hit one table. Imagine it's the lunch hour and everyone logs in to check their SumanSpace social network site. .5M users all reading that user table at once and then you have people creating new accounts as well as updating their existing data. The less wide you can make that base table, the better as you're going to be able to stuff more entries in an 8k page.

Plus, things like updates to Education or Location won't need to lock the main user table which could cause contention. Those locks would be on, what I assume, are less frequently accessed tables.

Specific to your tables. Education - I would look to design it as 2 - 3 tables. EducationType would be a lookup table describing the type of institution (grade school, middle school, junior high, high school, community college, military college, college, university, etc). EducationLocation to associate an institute of learning with a physical space. Something to consider here are universities with multiple locations like DeVry-is there business value in building out a location hierarchy with the parent in Downer's Grove and then all the satellite campuses listed? EducationObtained could be another lookup table, indicating the type of education obtained. For things less than the collegiate level, it'd have a value of graduated but the university level could have Bachelors, Masters, PhD, JD, Divinity (however abreviated) in Sciences, Arts, Fine Arts, etc. And of course, a cross reference table tying a user to their tenure (start and stop dates) at an institute.

Finally, you won't want an INNER JOIN as people may not have or may not choose to record their education or location information.

Those are my top thoughts before I go and get cleaned up. I'm sure @DennyCherry has some insight into the matter based on his time at MySpace.

share|improve this answer
    
@billinc: Mentiononing a user with @ inside an answer does not automatically ping him (not sure about this specific case, mods have some super powers) –  ypercube Dec 27 '12 at 19:43
    
@ypercube I'm aware of that, just figured if he glanced through the answer he'd spot his name. –  billinkc Dec 27 '12 at 21:47

I'd keep it in normalized form.
Later, if performance becomes an issue, you can always use materialized view (or indexed view in SQLServer terminology) which will improve performance of select queries. Even if you are not able to use indexed views out of the box (say, underlying query doesn't fit all SQLServer requirements) , it's possible to implement desired functionality using triggers (as you say, data is not gonna be updated frequently, so it should not be huge overhead due to triggers).

share|improve this answer

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.