Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm having an issue creating a little practice ERD. I'm trying to do some more difficult stuff, and I'm not sure what the standard way to organize it is.Here's a sample of what I have...

Basically, I don't know which of these is most correct. Also, I'm trying to add some data: "Last video chat; last voice chat; last im; last email".

Is has a better interaction word than friends?

Should I use:

  1. user->gold_friends->user; user->silver_friends->user,
  2. user->has->friend; friendship->is->gold (and how would these be connected, if so), or
  3. user->has->gold_friend; goldfriend->is->user?

Similarly, which do you prefer and why:

  1. user->emails->user; email->was at->date, the way silver and gold come out of type in the drawing?
  2. user->has->email_interaction, or
  3. user->has->interaction; interaction->was via->email

If I add a friend of friendID, should I be sure that the friendID is transferred as a derived key generated with the transaction? Why or why not? How does the process of friend adding work, programmatically? Is the key connected to the added user, and then transferred to the adding user, or is the key connected to the adding user and then sent to the added user, or does each user have a permanent key that is added to an array for each user at the same time when they add?

share|improve this question
1  
In light of dba.stackexchange.com/questions/11758/… do you still need an answer to this question? –  Joel Brown Jan 30 '12 at 2:23
    
@JoelBrown You make a fine point. It would be nice to have a specific example. On the other hand, there's not a lot of material on this topic on SE.... In light of your comment, I'll ask meta what they think. Considering keeping it open: I noticed the answerer mentioned some things that didn't come up at the other question, and I appreciate the exclusivity of that info. –  Wolfpack'08 Jan 31 '12 at 0:20
    
@Ronaldo Thanks. –  Wolfpack'08 Oct 2 '12 at 0:38
add comment

1 Answer

up vote 4 down vote accepted
+50

First, 'Friend' is probably not a different entity but just a relationship from 'User' to 'User' but your case may be special, I don't know. So about "if I add a friendID, should I have it as a derived key from user", assuming a friend is just another user - yes.

Additional info like 'last voice chat' & 'last im' shouldn't be part of a composite attribute, each 'last ' is meaningful by itself and a composite attribute doesn't help. If you want to know when user AAA lastly called anybody? then such info should be attributes of 'User', but if you want to know when user AAA lastly called user BBB? then such info should be attributes of the relationship between users AAA & BBB.

The type of relationship probably should be a simple attribute (enumerated) but it depends. If a friendship could be both silver & gold then it should be a composite attribute of boolean isSilver, isGold etc. But if is only silver or gold (or maybe normal) but only one of them, it should be a simple enumerated attribute.

Lastly, you should consider the different ways of how to define friendships, you probably want to read more about it in questions like these:

Good Luck!

share|improve this answer
    
Can I ask a little follow-up? I'm wondering, what about friend_instance? Is there any place for that in these diagrams? Also, when you say composite attribute, it seems to hold a different meaning. Do composites of isSilver and isGold have a parent, rating? Because if it's like lastTalked, lastBBSChatted, they're direct decedents of the relationship, IMO. –  Wolfpack'08 Jan 31 '12 at 0:27
1  
@Wolfpack'08 I'm not sure I'm understanding you but I'll try. As far I can understand you have no need for 'friend_instance' entity if you could explain why do you think you need it I may explain further. See here for composite attributes . Shortly, it's just an attribute to hold a group of attributes (not to be confused with multi-valued attribute) –  dvb Jan 31 '12 at 13:53
1  
About isGold etc. as I said in my answer, the type (or rating) of the relationship can be a composite attribute or a one simple attribute, it depends. –  dvb Jan 31 '12 at 13:59
1  
I think you can just do like [user]-<friend>-[user]. Draw a line coming back to user from friend. Then pipe type off of friend. –  Wolfpack'08 Feb 3 '12 at 6:57
    
@Wolfpack'08 Sounds like a good solution :) –  dvb Feb 3 '12 at 11:26
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.