Hello guys.

I am working on a simple instant messenger application similar to yahoo but not as complicated as yahoo messenger. I am not a fine database designer. I am basically a newbie in these database stuffs. I have uploaded my database structure about how i am going to implement the system.

Please guide me in finding the faults, drawbacks of the structure.

what might be the drawbacks, what might be the problems, is this thing correct ?

http://subrat.skcventure.com/db/my_er_diag.jpg


please note that here,

offline msgs are offline msgs like in yahoo messenger

memos are something like private message thing in this forum.

Thanks

Invictus

It appears at first pass to be pretty good.

I don't understand the Buddy_List table.

hi,

eerrm

both user_id and buddy_id are primay keys because both can represent each other coz both are users

about the buddy list thing,

i plan to stor my buddies like this:

lets say my user id is 100 and i store it

user_id buddy_id
100 234
100 211
100 932
234 100
211 100
932 100
234 512
512 234


but one thing is that if "x" has "y" in his buddy list, it doent mean that "y" will always have "x" in his buddy list because "x" might have been annoying to "y" and "y" deleted him

i plan to get the buddy list of a user ip by
select buddy_id from buddy_list where user_id=100

to find buddy of
select user_id from buddy_list where buddy_id=100


i think i have told u the whole senario.

please tell me if this is a proper way to do it or not.

if it is, is the structure for the buddy_list table seem to be okay ? :s

Thanks

Invictus

Okay, makes sense.

The notation you are using is a little different than those I am familiar with, but I think buddy list should have two relationships to users. One for buddy_list.user_id and one for the aliased user, buddy_list.buddy_id.

buddy_id is actually a foreign key.

Next, explain how Restricted works.

hi.

well i also think the restricted thing is kinda baseless there.

what i am wanting to do is that "restricted" table just contains a list of user_id OR ip_addresses that are forbidden to use the system.

I also dont think that should be linked like because it is just a list.

when the admin wants to ban a user or an ip address he just updates or insert a record in the "restricted" column.

he can either put a user_id OR ip_address OR both in the "restricted"
table.

Is there anyway i can express that in the diagram ? (i think the way i am doing it there is completly baseless. please guide me if i can actually show it in the diagram or is it that i dont have to show it and leave it as just a 3rd table that isnt in the diagram ?)


Thanks

Subrat

Would a boolean IsRestricted flag in the user table be adequate on one side? On the ip_address side perhaps a list of restricted ip addresses that are programmatically looked up would be adequate.

What's the difference between a contact and a buddy?

thnks for the reply.

i think the IsRestricted should be okay as well. i get what you are trying to tell me. thanks. that should be a good idea.

buddies are friends like they have friends in msn messenger.

contacts are the entries of the address book. contact category is something like in yahoo mail they have categories in address book such as friends and families.

contacts and buddies are different stuffs.

should contacts and contact_category be many-to-many ?

example:

A has B in his "friend" contact category

C can also have B in his "colleague" category

and A and C might have many categories.

so many categories should have many contacts right ?:s

Thnks

Yes, a many to many between contacts and categories does seem to make sense. Use an associative relationship.

Categories(CategoryID, attributes)
Contacts(ContactID, attributes)

CategoryContact(CategoryID,ContactID)

yeah i have done exactly that in my mapping.

thanks a lot man

Invictus