Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question
3  
Index well my friend. –  JeffO Apr 17 '12 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. –  Emmad Kareem Apr 18 '12 at 23:39
    
I don't get the "timeline" to "follow" relationship. What does this represent? –  James Anderson Apr 19 '12 at 6:18
    
It is like twitter's firehose filtered by user. –  romaninsh Apr 19 '12 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 Jun 20 at 10:47

5 Answers 5

up vote 2 down vote accepted

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.

share|improve this answer
1  
Thanks Vitalij, Your answer was the most helpful. I will research graph databases more, but it seems like what I need. –  romaninsh Jun 18 '12 at 8:14

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                | ...   | user@somewhere.com

This describes an activity instance (ID #1): a user (object #84, context data "user@somewhere.com") 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.

share|improve this answer
    
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 Apr 17 '12 at 15:56
    
Why doesn't it seem scalable? –  FrustratedWithFormsDesigner Apr 17 '12 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 Apr 17 '12 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 Apr 17 '12 at 17:22
    
exactly. I've added clarification to my original question to highlight that. –  romaninsh Apr 17 '12 at 21:38

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.

share|improve this answer
    
I am using the ORM, but I need to decide how to structure my data model first. –  romaninsh Apr 17 '12 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 Jun 17 '12 at 23:11

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.

share|improve this answer
    
Thanks. I'm already using Agile Toolkit ORM which takes advantage of OOP. agiletoolkit.org/intro/1 –  romaninsh Apr 17 '12 at 21:29
    
updated with performance hints –  alex Apr 18 '12 at 21:25

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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