SQL schema

Aug 5, 2008 at 8:54 PM
Edited Aug 5, 2008 at 10:19 PM
Firstly, you don't need to script the aspnet_* tables, they can be taken as read, since they’re generated by aspnet_regsql.exe

I like how the tables are all prefixed with "bmv"

Why a numeric Id on [bmv_Blogs] and uniqueidentifier on [bmv_Posts]?

I can think of three possible datetimes for a post: datetime of creation, datetime of last update, and datetime of post (for futuredated/pastdated posts)

With regards users, the asp.net membership system identifies them with a unique guid, but the blog system may want to store a table with exta data on users in a "users" table. I'd have at least name, nickname, gender and bio in order to later have the data for a basic rdf:foaf document about them (http://xmlns.com/foaf/spec/), but I'm sure other arrangement is also possible.

Can any user post to any blog? If not, then a joining table "users_blogs" could be used to identify which users are allowed to post to which blog.

Have you considered user roles? i.e. which users can create blogs, which users administer which blogs or are just logged-in commenters.
Aug 7, 2008 at 8:46 PM
Good points.

So a few answers:

  1. My thinking on the ids was that for simple IDs such as for a blog id, I am probably not going to have very many of them so a simple auto-incrementing integer works well.  For blog posts I was intending to use the guid for permalinks and so I wanted a unique way of identifying the post.
  2. As for dates, that sounds possible.  I want to avoid adding to much complexity unless it is actually going to be implemented.  One thought also is perhaps to separate PostDate and PostTime so that I can easily index on post date since most searches will be done on date.
  3. As for membership, I will look at the foaf spec and consider your users table.  I want to support muli-authors per blog so I think I will add a blog owner table, where the blog owner can be an individual or a group.
  4. Right now anyone can post to a blog, but obviously I need to add blog ownership
  5. I will use membership roles.  A member will have to be a blog owner to edit a blog.  We will also have admin roles and I don't know if you should have to log in to leave a comment.  I believe most blogs don't require this.
One of the goals with getting this going was in the spirit of agile programming was to start with the simplest thing that was useful: namely viewing blog posts.  Now that I have that working, more will come.
Aug 8, 2008 at 8:17 AM
Interesting re Guids vs ids. I'll have to think about usability of numeric vs. guid ids for permalinks.

Most blogs don't require login for comments. I'm most familiar with wordpress, where comments can be made anonymously or from a logged-in user (you can probably allow/disallow anonymous comments in the blog configuration). Anonymous comments are subject to more scrutiny, typically not displayed immediately, but held in a moderation/spam queue for approval before they are shown.

It's totally right to not target these kinds of features in an early version. But also you may want to accommodate the possibility in any design work done now.

OpenId adds a third category, people who have a verified identity, but are not a local one.
Aug 12, 2008 at 10:11 AM

I have mailed you some code and sql. If the attached zip file is blocked, then I can upload it as a patch here.