Please help me to solve this question

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.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Please help me to solve this question

Author

Topic

roshana
Starting Member

31 Posts

Posted - 2009-10-30 : 00:36:28

HI All,
I have an interview question

“We would like to store a list of user’s in the database with their corresponding network of friends, groups, as well as any messages which are swapped between them. A user consists of a username, password, first name, last name and profile picture. A message is a string. User’s can have many friends but usually start off with none or only 1 friend. A user can join or create a group and there can only be 1 creator for any group”

Please draw up a draft database schema for the above description with as much detail using the following annotation

TABLE NAME:
TABLE ATTRIBUTES: column1, column2, column3
FOREIGN KEYS: column1 to .column1

Thanks in advance
Roshana

krishnarajeesh
Yak Posting Veteran

67 Posts

Posted - 2009-11-06 : 14:00:05

Hello,

Could try some thing like this...just created fast...pls review.

Tables with columns

USERS
UID -> PrimaryKey(Pk)
UName
UPassword
UFirstname
ULastName
UProfilePic

GROUPS
GID -> Pk
GName
UID

MESSAGES
MID -> Pk
MContent

FRIENDS
UID -> Compositekey
FID -> Compositek

FRIENDS_MESSAGES
UID -> Compositek
FID -> Compositek
MID -> Compositek

FOREIGN KEYS

GROUPS.UID -> USERS.UID(Pk)
FRIENDS.UID -> USERS.UID(Pk)
FRIENDS.FID -> USERS.UID(Pk)
FRIENDS_MESSAGES.UID -> USERS.UID(Pk)
FRIENDS_MESSAGES.FID -> USERS.UID(Pk)
FRIENDS_MESSAGES.MID -> MESSAGES.MID(Pk)

Thanks,
Krishna
www.SQLServer.in

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources