A newbie question about db design

Question

Post reply

A newbie question about db design

tomiz

SSCommitted

Points: 1842
More actions
January 7, 2003 at 12:24 pm

#79613

Hello!

I'm trying to make a buddylist feature to my site. I've come up with a REALLY simle solution like this:

memBuddy

memId

buddyId

Now... There is no PK in the table. I know that can't be good, right? How should I do it then? For example, how is "Your virtual briefcase" done on this site?

Thank you!

/Tomi

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 1

I'd set the memid as the PK if that's what it is. We assign an "ID" to every member and use that to track your posts, breifcase, etc.

Steve Jones

sjones@sqlservercentral.com

http://qa.sqlservercentral.com/columnists/sjones

http://www.dkranch.net

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

dj_meier SSCrazy Points: 2732 More actions · Answer 2

Actually your primary key could be both columns (memID, buddyID) together. It is probably best to create the primary key as non-clustered, due to the randomness of inserts. Another option is to just create an identity column, but I prefer the compound primary key method.

I'm assuming each member can have multiple buddies, and each buddy can be associated with multiple members.

-Dan

Edited by - dj_meier on 01/07/2003 1:54:27 PM

-Dan

tomiz SSCommitted Points: 1842 More actions · Answer 3

Hi!

So what you're saying is that if (that's a big if) I had 1 million users and each had 10 buddies... Then there would be 10 million rows in that singe table? The records would look like this:

memId buddyId

12123 2342

9586 234234

9586 21

9586 996

.

Isn't there a better way or is this how it's generally done?

Thanks!

/Tomi

mromm SSCarpal Tunnel Points: 4301 More actions · Answer 4

Tomi, that is the best way.

You are talking about many-to-many relationship in this case. There are three logical types of relationships: one-to-one, one-to-many, many-to-many. The last one is suported by SQL Server by utilizing the compound primary key where one column is a foreign key to one table (member) and another one is a foreign key to another table (buddy).

Antares686 SSC Guru Points: 125444 More actions · Answer 5

Antares686

SSC Guru

Points: 125444

January 7, 2003 at 3:17 pm

#445788

One thing to also keep in mind with Compound keys make sure when the index is created the most unique value column is listed first as the stats are based only on the first column. The less unique the value the lower the stats, the lower chance it may get used in some queires.