Best design for Hierachical User Subscription to manydifferent things? 1

[jpo245]

Hi everyone,

I have a db design issue im hoping someone could give me advice on.

Currently:
Message system allows users to "subscribe" to messages that other users submit. So User1 can subscribe to User2's submitted messages. These messages will appear in User1's inbox.

Groups also exist. Each group has a memberbase of users, and each group has a related theme. For example, a group called "MUSIC LOVERS" can have members who are musicians. Users can also subscribe to "GROUPS" so any messages posted by these users in these groups, can also appear in the user's INBOX.

Currently i have 2 tables that link up:
A) USERID / USERID (facilitate user-user subscription)
B) USERID /GROUPID (facilitate user-group subscription)

However, i foresee in the near future, i will add many many more subscription options. THus i was thinking, rather than having to create many new tables for each type (group/user/etc) of subscription, would it be feasible to create just ONE table that will universally cater to ANY type of subscription (now or in the future).

My idea was to create a linking table with fields
A)USERID / String Defining Subscription

So for example:
User A subscribes to:
Group: "Music"
Group: "Cars"
User: "UserB"
User: "UserC"

The link table would then have entries as follows (assuming USERA's USERID is 0001):

USERID / Subscription String
0001 / #Music
0001 / #Cars
0001 / $UserB
0001 / $UserC

To find the list of users that userA subscribed to, i would

1. filter for all "0001s" (thus having a list of all the Subscription strings belonging to 0001)

2. filter these strings for all "$" (this symbol denotes a subscription to another user) I would thus obtain "$UserB" and "$UserC"

3 Extract the "USERX" portion from "$USERX" and go to the user table and run a search for "UserX" in this case "UserB" and "UserC" grab their respective USERIDs.

Same would be for the groups as well.

In fact, I could even define the Subscription string like this: #Cars$UserB.

This would mean that a person can subscribe to messages from the "Cars" group that are ONLY sent by "UserB".

I feel this would make my script future proof for watever type of subscription (possibly even hierachical) options.

I was wondering if there were any other more efficient methods of accomplishing this, esp since this may require a lot of queries to just accomplish one function, possibly loading the server qutie a bit.


Any thoughts on this?

I do apologise for such a long post.

 

[TonyGroves]

Some excellent ideas there.

However, I would be concerned about the "#Cars$UserB" system. It would make searching that field very inefficient. For example, to search for all users
subscribed to the user "jpo245", you would have to use the clause [tt]WHERE subscription LIKE "%#jpo245%", which would prevent an index being used. And even that clause would not be accurate enough, as it would also match against people subscribed to user "jpo245678"; you might have to use a regular expression for accuracy.

I would be more inclined to go for a multi-field subscription record, containing the fields User, SubscribedGroup, SubscribedUser, and so on. That way, each
field can be indexed for fast searching. And, you can use the value NULL in any Subscribed... field to specify "all". So, you can have a record for somebody who is subscribed to messages related to "cars" from "userB", and another record
for somebody subscribed to anything from "userB".

 

[jpo245]

Dude, that is pure awesomeness once again.

You managed to explain in 2 minutes an even better solution.

Multifield approach that can easily cater for further subscription options... why didnt i think of that! yargh!

Thanks once again!