I am working on a Chat website. I am using asp.net and SQL Server 2008
The idea will be same like normal chatting websites. My problem is, I do not understand how to create a good database design.
I have good knowledge of vb asp.net with SQL Server.
In my project, I created a table for adding new user with ID, uName, uPassoword and uDetail columns.
Then.... I created a table named User_Friends (uName , uFriendName).
Suppose when user "abc" add new friend it save the relation uName = "abc" and uFriendName ="xyz" in this table, same all other users will do to add there friends and all that data will go in one table (User_Friends). All detail that who is whose friend information is in this User_Friends table.
My question is:
- But what if there are 10000 users or more and each user add many friends as friend in DB.
- Same thing if I want to save each user chat in DB. Should I use one table for all users chat history or something else.
Then WILL IT BE A SLOW PROCESS TO GET DETAIL FROM ONE TABLE OR NOT?
Please advice me how to design DB or give me some useful links for help.
saving chat data into database is a space consuming task, i would prefer you to do it this way:
user A chats with his friend B... once the chat window is closed make a text file of it
and save it into a folder structure like Year/Month/Date with file name as 'userid+user friend id' eg: AB.txt
For every day keep appending data to the same text file. so in future we will show all chats done in a day in a single file.
Add a table chat history with Id,File Name,Created Date Time. Check if that file name already exists in DB if present take that file and append data to it
else insert a row with fully qualified path into DB and save chat data into it
it all depends on how you want to use the stored data.
for the user structure I would recommend to make relationship (friend,blocked,...whatever) in a separate table i.e.
table "user": user_id, nick, name, email, status, ....
table "relation": user_id1, user_id2, relation_type
it is more flexible since you are not limited how many users are on any side of the relationship but it also add some complexity.
regarding message history I would record each message in table like:
table "messages" message_id, send_by, send_to, timestamp, message_text
and do cleaning of messages that are older than .... I suppose you don't need whole history anyway.
regarding performance it is slightly different matter. Databases are designed to handle a lot of data but if you don't index them properly then it will be slow. Finish you table design first. And think about indexing later - based on what type of queries you will execute.
