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

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

Is has a better interaction word than friends?

Should I use:

• user->gold_friends->user; user->silver_friends->user,

• user->has->friend; friendship->is->gold (and how would these be connected, if so), or

• user->has->gold_friend; goldfriend->is->user?

Similarly, which do you prefer and why:

• user->emails->user; email->was at->date, the way silver and gold come out of type in the drawing?

• user->has->email_interaction, or

• 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?

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:

• https://stackoverflow.com/questions/3009190/mysql-friends-table

• https://stackoverflow.com/questions/6618701/storing-friends-in-database-for-social-network

• https://stackoverflow.com/questions/3120591/mysql-friends-table-structure-help

Good Luck!

StackExchange Database Administrators Q#11365, answer score: 4