I planning to creating a simple social network but confused about the database design
Multi Pivot
user_friends
user_id
friend_id
user_follows
user_id
follow_id
status_mentions
status_id
user_id
status_likes
status_id
user_id
Single Pivot
user_selves
user_id
self_id
type_id
status_users
user_id
status_id
type_id
type_id will be filled by small integer, in user_selves 1 = friend, 2 = follow, status_users 1 = mention, 2 = likes
what are the pros and cons of that two db design?
j
stuff ;)
well all of them suck as soon as a certain size is reached. :)
but lets go for the obvious first -> index writes.
The single pivot should be the same length as the sum of the multipivots. So the index with write will get slower faster.
Usually a flat table usually outperforms a deep table since you can use filters on those tables. So you could flatten things even more and just use the table as 'edges' of the graph and have fast filter operations.
I personally do recommend a graph database for such things .... they are built to display n:m:x:y:z relations so every form of timeline is so much easier to build with them.
But if I want to keep the normalized forms and isolation of data multipivots seam to be the right choice.
It's a trade .... more tables, different memory behaviour, different indexes. Take the one you're more comfortable with.