Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2003-06-25 : 06:15:28
|
| I am trying to create an "interests" part on a website where users can enter keywords to be linked up with other members with similar interests. I am a bit unsure as to the best way to go about this, and want to be absolutely sure I take the best route as this is a really crucial point. Scalability and speed are everything as I expect alot of use.The main part I am confused about is storing the interests. Lets say for instance my interests are : "sqlteam, movies, hockey, beer". Those are 4 separate interests.Should I have a table defining all interests? For example (tblInterestDetails):InterestID Interest1 Sqlteam2 movies3 hockey4 beerI am the following useruserID=500, nameOnline = mike123So I would have to create a table as such (tblInterests):UserID InterestID500 1500 2500 3500 4I would have to do all the checks to see if the "interest" existed and create it if necessary. Or would I be better off just storing text and userID all in one table? The things I want to do that I can think of as of right now are :I want users to be able to search for people with a certain interestalso to see who has the most number of common interests with the user. Which way do you think would be better/ faster? Do you see any benefits/ downfalls to the different ways? I expect to have possibly 1000's of different "interests", while growing tens of thousands of users with possibly many interests each so there would alot of rows in the table listing all users and their interests.Any advice is greatly appreciated. If I need to explain better let me know, its 4am hopefully it makes sense :)Thanks alot guys!Mike123 |
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2003-06-25 : 06:52:04
|
| Hi Mike,Very interesting question and I wil do my best to give my version of answer.Firstly, your suggested approach is perfectly reasonable.May I suggest that you group your intersts and perhaps introduce a group table?Someting like the following might work well for you :InterstTableInterstID, DescriptionInterstGroupInterstID, GroupIDGroupsGroupID, DescriptionYour first table InterestTable will hold all your intersts such as SQLTeam, Golf, Soccer etc...You can then group your intersts, so Golf, Soccer, Tennis would fall under Sports etc..InterestGroup would hold the InterstID and the GroupUID it belong to. So if Soccer has an ID of 1 and the Group Sports has an ID of 2 youwould have a record of 1, 2 in the InterestGroup table.Finally, your Groups table will hold all the GroupIDs and perhaps a description + whatever you decide to have in there.Hope this helps.----------------Have a good day! |
 |
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-06-25 : 07:18:11
|
| I think it will be pretty hard to tell the difference between these two ways.From the point of view of a client/user I mean. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-06-25 : 07:38:22
|
quote:
I expect to have possibly 1000's of different "interests", while growing tens of thousands of users with possibly many interests each so there would alot of rows in the table listing all users and their interests.
Then your suggested design:UserID InterestID 500 1 500 2 500 3 500 4 ...will be the most efficient way to store that data. Make both columns the primary key, putting UserID first, and create a 2nd index on InterestID, and you're all set. If you had 10,000,000 rows in that table (10,000 users x 1,000 interests...everybody is interested in everything :) you can still query that table at lightning speeds. You've only got 2 int columns, 8 bytes per row, you can put about 1,000 rows on a data page. Even if you add an interest groups table you can still use the above design to store the user's interests.Don't get hung up on having millions of rows, if they are narrow rows and the columns are indexed then it will perform very well. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2003-06-25 : 08:17:07
|
| in the (and excuse the pun) interest of making something useful....i would also suggest that you don't let 'ordinary' users add items to the master list of interest themselves....what could be done is that they submit a category 'to the administrator' for inclusion....and pending approval by the admin, the category is allowed into the database....but if rejected....all those who associate themselves with that category (prior to rejection) would be notified of the (pending state of the category) and rejection...with a suggested alternative.otherwise, you'll get football, footballing, fotbal, footballers, futball....etc....and then the choice of categories may drift too wide to be useful to you or the users.also you may get some (PC) undesireable catgegories......if you allow a free for all.Also you may need to consider the multi-lingual aspects of this....Football in Europe is Soccer in the US and Futbal in parts of Spain/Italy/Portugal (excuse the poor geography, but it's my language/spelling skills that are amiss)You may need to define a basic set of nouns with alternative spellings associated, based on language or regional interpretation of words....good luck with the activity! |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2003-06-26 : 05:33:03
|
| thanks for all the excellent advice!, i feel much more confident going into this now..thanks againmike123 |
|
|
|
|
|
|
|
|
database design help