The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

DB design for mult-site blog

Just wanted to get your thoughts on designing a multi-site blogging application.  This is more for learning purposes than a real application. 

Also, I'd like to have Authors be able to be able to post to multiple sites. Other than that it's a pretty standard setup. 

So, here is what I'm thinking, with Sites_Authors being a join table between Sites and Authors.

Table:
Sites
Sites_Authors
Authors
Posts

Site:
id, name

Sites_Authors:
site_id, author_id

Authors
id, name

Posts
id, author_id, title, post_body

In this setup, Posts belong to Authors, which makes sense, however, I will also have blog sites where I will be displaying posts - Is there any need or benefit to having the Posts table contain a site_id ?

Or feel free to comment on this design at all. 

Thanks, I appreciate it.
Jim Jones Send private email
Sunday, February 18, 2007
 
 
"I'd like to have Authors be able to be able to post to multiple sites. "

Presumably you don't want each post by an author to appear on all "sites" that author can publish to, but only in the context of that site.  In that case, you need to tie a post to a site as well as to an author.
Dan Fleet Send private email
Sunday, February 18, 2007
 
 
I spent most of 2005 doing this...

You need to track the post on *every* site in addition to making sure things stay up to sync, etc.  The other big nasty thing is making sure that the permissions are designed and configured... not just for publishing, but for whatever editorial workflows may be in place.
KC Send private email
Monday, February 19, 2007
 
 
Yes.

HTH
Aaron
Aaron DC Send private email
Monday, February 19, 2007
 
 
Just the primary keys...

Users
-----
user_id

UserRoles
---------
user_id
site_id
role_id

Sites
-----
site_id

Roles
-----
role_id

RolePerms
---------
role_id
perm_id

Perms
-----
perm_id

Users can belong to multiple Roles per site.  Roles are granted permissions ("post comment" "delete comment"), but are named globally - I didn't want to have sites creating their own roles (mostly because that makes the SQL even messier and makes rollouts harder).  This structure makes the SQL a little messy when you want to match users to sites.  You also have to trap for the first time they sign up so they are assigned the proper role for that site.
Cory R. King
Tuesday, February 20, 2007
 
 
You might want to check out communityserver.org which offers an open source multi user multi site blogs forums photo galleries etc package.  Its the basis for all kinds of huge forums, including the official Xbox live forums, the channel 9 forums, etc.  You can take a look at how they handle their tables.
system of a Don Send private email
Tuesday, February 20, 2007
 
 
Or you could just use Drupal.
OK C0mput3r
Wednesday, February 21, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz