6

I am working on a web application which operate with different types of objects such as user, profiles, pages etc. All objects have unique object_id.

When objects interact it may produce "activity", such as user posting on the page or profile. Activity may be related to multiple objects through their object_id.

Users may also follow "objects" and they need to be able to see stream of relevant activity.

Could you provide me with some data structure suggestions which would be efficient and scalable? My goal is to show activity limited to the objects which user is following I am not limited by relational databases.

Update

As I'm getting advices on ORM and how index things, I'd like to again, stress my question. According to my current design model the database structure looks like this:

enter image description here

As you can see - it's quite easy to implement database like that. Activity and Follower tables do contain much larger amount of records than the upper level but it's tolerable.

But when it comes for me to create a "timeline" table, it becomes a nightmare. For every user I need to reference all the object activities which he follows. In terms of records it easily gets out of control.

Please suggest me how to change this structure to avoid timeline creation and also be abel to quickly retrieve activity for any given user. Thanks.

Improve this question
6
  • 5
    Index well my friend.
    – JeffO
    Commented Apr 17, 2012 at 19:15
  • I see that your description is not exactly consistent with the picture. For example, you say "I am working on a web application which operate with different types of objects such as user, profiles, pages etc.", but the picture shows users and objects like 2 separate entities. Also, you have profiles part of the 'objects' entity, yet you have users in the 'object' entity but with no association to either users or self reference to 'object'. Maybe you want to clarify the meaning of each entity a bit.
    – NoChance
    Commented Apr 18, 2012 at 23:39
  • I don't get the "timeline" to "follow" relationship. What does this represent?
    – James Anderson
    Commented Apr 19, 2012 at 6:18
  • 1
    It is like twitter's firehose filtered by user.
    – romaninsh
    Commented Apr 19, 2012 at 9:13
  • Try github.com/tschellenbach/Feedly, this is an opensource design for timeline management backed by either Redis or Cassandra (in Python using DRF and Django), you can either use it or check the multitude of resources they used in designing the library (directly from their readme under section Background Articles)
    – JohnnyM
    Commented Jun 20, 2014 at 10:47

5 Answers 5

Reset to default
3

You wrote that you are not limited to relational databases. You could consider using a graph database, like neo4j. Graph databases are particularly well-suited for situations like yours where relations are more important than, say, averages and sums.

You can find an introduction here and a working example here.

A more complex, but more efficient structure that you could use is graphity.

Improve this answer
1
  • 1
    Thanks Vitalij, Your answer was the most helpful. I will research graph databases more, but it seems like what I need.
    – romaninsh
    Commented Jun 18, 2012 at 8:14
3

It's hard to say without knowing too much more, but it sounds like you want something very generic. Maybe something like this would help:

activity
--------
  activity_id  - the ID of the activity
  participating_object_id - ID of a participating object
  activity_type_id - ID of the Type of activity
  activity_datetime - When this activity occurred
  data - context data of the activity

This would let you have things like:

activity_id | participating_object_id | activity_type_id | date  | data
------------+-------------------------+------------------+-------+-----
1           | 2                       | 3                | ...   | this is a post
1           | 84                      | 3                | ...   | [email protected]

This describes an activity instance (ID #1): a user (object #84, context data "[email protected]") created a posting on a forum (activity type 3) whose object ID is 2, and the posting contained the text "this is a post".

You'll probably need to expand on this as your situation requires.

...actually, now that I think about it, the data column is probably not needed, since you can get any data you want from your base object table (whatever it looks like) by querying it using the values in participating_object_id.

Improve this answer
7
  • i do have activity table and then "object_activity" which joins multiple objects into a single activity. I wouldn't have problem to design the structure, but my design doesn't seem to be very scalable, especially when you need to determine which "activities" i need to show to the "users"
    – romaninsh
    Commented Apr 17, 2012 at 15:56
  • Why doesn't it seem scalable?
    – FrustratedWithFormsDesigner
    Commented Apr 17, 2012 at 16:12
  • when you have 1000 users and each is following 100 objects then either each objects needs to add "timeline" event into the queue of each user every time or user need to iterate through all 100 objects to collect the updates.
    – romaninsh
    Commented Apr 17, 2012 at 16:50
  • @romaninsh: If I understand you correctly: You are concerned that it will be very time-consuming for a user to get updates on the 100 objects that they are following?
    – FrustratedWithFormsDesigner
    Commented Apr 17, 2012 at 17:22
  • exactly. I've added clarification to my original question to highlight that.
    – romaninsh
    Commented Apr 17, 2012 at 21:38
2

One possibility is to use "inheritance", esp. if the objects you refer to have common fields. The schema looks like this:

followables
    followable_id      primary key
    -- common fields

users
    user_id            primary key references followables(followable_id)
    -- user specific fields

pages
    page_id            primary key references followables(followable_id)
    -- page specific fields

...

random_entities
    ...
    followed_id        references followables(followable_id)

This means that both pages and users "are" followables, have followables' attributes and can be referenced alike.

ORMs often create schemas like this to model inheritance, and handle the joining for you.

Do not worry about inefficiency until you can prove it is a problem.

wrt. to efficiency. Write a simple SQL script that inserts test information up to the number of rows you deem you need. Write your queries (remember, you don't want to query your entire timeline, you will normally LIMIT your queries), check their execution speed- with the stuff you mention I'm guessing the performance will be satisfactory. If not, post your queries and the outputs of EXPLAIN.

Improve this answer
2
  • Thanks. I'm already using Agile Toolkit ORM which takes advantage of OOP. agiletoolkit.org/intro/1
    – romaninsh
    Commented Apr 17, 2012 at 21:29
  • updated with performance hints
    – alex
    Commented Apr 18, 2012 at 21:25
1

Denormalization is going to be your friend here -- have you considered just making a static timeline table that you can SELECT from easily?

I would also strongly consider non-relational data stores here -- they tend to fit this sort of problem well.

Improve this answer
0

Why not use a ORM that can build the scheme for you around your classes? This way you can workout the details at a higher level of abstraction, let the ORM design the schema and then review what its done to change/tweak it based on performance.

Improve this answer
2
  • I am using the ORM, but I need to decide how to structure my data model first.
    – romaninsh
    Commented Apr 17, 2012 at 18:46
  • This is the sort of problems where ORMs tend to fall flat on their face as they tend not to handle complex scale issues.
    – Wyatt Barnett
    Commented Jun 17, 2012 at 23:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.