ERD via Chen's notation: How do I organize multiple 'last done' entries?

Question

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.

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".

Three Questions

• I'm not sure if I should organize it off of has, like type stems off of has, in the first drawing. Or should I stem it off of friend, like in the second drawing?
• I'm not sure if I should be direct, like silver and gold come directly out of has in the third drawing. Or should I stem indirectly, like last->chatted/emailed/..., the way silver and gold come out of type in the first and second drawing?
• If I add a friendID, should I have it as a derived key from user?

Answer 1

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:

• http://stackoverflow.com/questions/3009190/mysql-friends-table
• http://stackoverflow.com/questions/6618701/storing-friends-in-database-for-social-network
• http://stackoverflow.com/questions/3120591/mysql-friends-table-structure-help

Good Luck!