Wall Post discussions.

Hi, I have designed a Wall Post feature for my fun site. Just want to see if there is other approaches that I should consider.

Right now, I have 3 tables.

1. WallPost
2. WallPostComment
3. WallPostSubscribers

The first two tables are intuitive.

The 3rd one is where I hope to get feedback from.

-- --------------------------------------------------
-- Creating table 'WallPostSubscriber'
-- Friends will have these entries duplated from the original post.
-- --------------------------------------------------
CREATE TABLE [WallPostSubscriber] (
    [WallPostId] bigint            NOT NULL,
    [SubscriberId] int            NOT NULL,
    PRIMARY KEY ([WallPostId], [SubscriberId]),
    CONSTRAINT [FK_WallPost_WallPostSubscriber] FOREIGN KEY ([WallPostId]) REFERENCES [WallPost] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_UserProfile_WallPostSubscriber] FOREIGN KEY ([SubscriberId]) REFERENCES [UserProfile] ([UserId])
);
GO

-- I just remmeber this one is covered by PK. CREATE INDEX [IX_WallPostSubscriber_WallPostId] ON [WallPostSubscriber] ([WallPostId] ASC);
GO
CREATE INDEX [IX_WallPostSubscriber_SubscriberId] ON [WallPostSubscriber] ([SubscriberId] ASC);
GO

This is SQLCE, so, I cannot ON DELETE CASCADE for the second FK, just FYI. Basically instead of computing what you should see on your wall, I just duplicate the entries for all the friends upon posting. Also when friendship is created, I will create the entries as well.

Is this any good? It feels so primitive. Is there any clever way than this?

@magicalclick: Have you considered LocalDB? It has all the benefits of SQLCE without the limitations.

-Josh

That's interesting!!!! I am downloading WebMatrix 2 since it is said that they replaced SQLCE by LocalDB. I will see where it goes. This opens up triggers and so on, which opens up different coding solution for me.

LOLz, I am not sure why I even bother with data limitations. My hosting is 2GB MAX  

Update: I am reverting back to WebMatrix1 for now. It doesn't work with my VS2010. And it is going to delay my progress. I will get WebMatrix2 when it is RTM and then, thinking about update my site.

Why are you going for a relational database?

@Maddus Mattus:

So what do you suggest? I really want to know other appraochs before I go too deep.

@magicalclick:Maddus is referring to something in the NoSQL arena, I assume.  I've heard good things about RavenDB, but I've never used it.

@Maddus Mattus: Why wouldn't you?   

While RDB are hugely over-used and very limiting compared to other formats (e.g. XML/JSON); this seems like a highly appropriate use of them. The flexibility offered by a RDB when the schema is fairly static and the data stored in simple relations (1:1, 1:*) is easy to see and quantify, I'm not sure what the benefits of a different storage medium would be given the scenario.   

@magicalclick: NoSQL db, no need for referential integrity. It's fast, cheap and can scale. A good example is Azure table storage. Next version of the ch9 app, I'm going to put the data in there and expose it via WebAPI (if they get decent WP7 support).

@ScanIAm: exactly

@ManipUni: Doesnt scale

@Maddus Mattus: Relational databases don't scale? What? You realise excluding a few niche applications all of the world's largest database deployments are a relational database? 

@ManipUni: yes, I realize.

But "we've always done this this way" is not a reason to chose for relational databases.

You always need to make the right decision for the right reasons.

@Maddus Mattus: I agree. That's why a relational database using SQL as an interface language is the right choice in this situation. You've yet to make any argument for why the alternative is better. Scalability is not one, since given the scenario, we can assume it won't scale beyond tens of requests per minute. 

@ManipUni: I've made no such suggestion, I merely asked why.

Doesn't Facebook run on an RDBMS? I don't think scalability is a real issue.

It would be fun to make a list of options. So far we have:

1. RDBMS
2. NoSQL
3. XML
4. plain text file

Given the relational characteristics of the data we're storing, an RDBMS seems to be the obvious choice. What are the benefits of the others for this wall post system?

Hello, Maddus, do you have any good NoSQL DB that works with ASP .NET hosting services? Maybe I can give it a try.

But, can you give me a specific reason why my solution is lacking. Even for NoSQL, I think having the WallPostSubscription table would help improving the performance because it skipped the friendship stage.

@Maddus Mattus: "Doesn't scale" is the typical NoSQL hipsters response when what they actually mean is "Isn't cool" but don't want to say that because it sounds like a ridiculous justification (and it is).

Relational databases scale up perfectly well and do so to enormous sizes, what they are not so good at is scaling out. So the question you need to ask is whether that's important to the project at hand. If you have both the need and resources to provide a scale out solution, then a NoSQL approach is probably justified but if not then a traditional RDBMS may well be a better overall fit (if less cool).

In this specific case, the issue of scalability really isn't important at all.

@magicalclick: my only experience is with azure table storage, so I cannot help there. I was not my purpose to derail your plan, I was merely wondering if you considered it.

@AndyC: good points, ofcourse rdbms will be the standard for quite sometime.

But do you really really need it in a wall post scenario? What is the added value of it?

so..... Anyone has any other idea for the wall post? Like how is the Facebook or tweeter do it ? How do they strike balance between storage and performance? Thank you.

I would write the first pass on sql, because...

a) it's relatively easy
b) you can demo little bits of code and almost anyone can read and understand it
c) conceptually, you can migrate from one sql to another easier than one noSql to another

-Josh