I'm creating an application that will involve customers 'following' each other peoples activity, within the twitter sense, however i am not so familiar with database/query design/efficiency. Exist guidelines for controlling this, issues to prevent, etc.? I gather this can produce a large strain on the db otherwise done correctly (and maybe even then?).

Whether it is important chances are that individuals will 'follow' merely a relatively few people (but you might have numerous fans). This really is not sure, and that i wouldn't wish to rely on it.

Any advice gratefully received. Thanks.

Pretty easy and simple related to full normalisation. For those who have a table of customers, each having a unique ID, you'd possess a TABLE_FOLLOWERS table using the posts, USERID and FOLLOWERID which may describe all of the fans for every user like a face to face to a lot of relationship.

Despite countless assosciations on the half decent database server this can succeed and fast as lengthy when you are utilizing a good database (IE, not MS-Access).

The model is rather simple. The issue is in how big the Subscription table if you will find a million customers, and every are members of 1000, then your Subscription table has 1 billion rows.

alt text

That is dependent on the number of customers you anticipate to support the number of fans you anticipate customers to possess and what kind of funding/development-effort you have a much use of when your solutions towards the previous questions prove positive.

For any small-scale project I'd likely disregard the database, design the applying like a simple object model with User objects that conserve a List[followers]. Ensure that it stays all in RAM for normal operation and employ an ORM to persist to some database periodically (most likely postgresql or mysql).

For any bigger project I wouldn't be utilising a relational database whatsoever but precisely what I'd use would rely on the particular particulars from the project.

If you're only attempting to spike the idea, opt for the ORM approach but, bear in mind it will not scale.

You most likely should read http://highscalability.com/ and it is articles how this really is handled through the large sites.