0

I have a system where actions of users need to be sent to other users who subscribe to those updates. There aren't a lot of users/subscribers at the moment, but it could grow rapidly so I want to make sure I get it right. Is it just this simple?

create table subscriptions (person_uuid uuid,
    subscribes_person_uuid uuid,
    primary key (person_uuid, subscribes_person_uuid)
)

I need to be able to look up things in both directions, i.e. answer the questions:

  • Who are Bob's subscribers.
  • Who does Bob subscribe to

Any ideas, feedback, suggestions would be useful.

Jay
  • 19,649
  • 38
  • 121
  • 184

2 Answers2

1

Those two queries represent the start of your model:

  1. you want the user to be the PK or part of the PK.

  2. depending on the cardinality of subscriptions/subscribers you could go with:

    1. for low numbers: using a single table and two sets
    2. for high numbers: using 2 tables similar to the one you describe
Alex Popescu
  • 3,982
  • 18
  • 20
0

@Jacob

Your use case looks very similar to the Twitter example, I did modelize it here

If you want to track both sides of relationship, I'll need to have a dedicated table to index them.

Last but not least, depending on the fact that the users are mutable OR not, you can decide to denormalize (e.g. duplicate User content) or just store user ids and then fetch users content in a separated table.

I've implemented simple join feature in Achilles. Have a look if you want to go this way

doanduyhai
  • 8,712
  • 27
  • 26