database schema for a multiuser/multiblog system

SelArom · #1 (**permalink**) 03-06-07, 06:23

hi everyone, I'm wanting to make a simple blogging system, where a user can create their own blog.

But I also want a user to be able to create multiple blogs. This means that one person can have many blogs and also many people can have one blog. I'm wondering the best way to store this information in the database. I'm thinking one big table for all the posts, but how can I map a specific post to the specific blog of a specific user?

I thought about using something like how wordpress uses table prefix, but that requires that I make my queries dynamically and all my research says this is a bad idea. but then how am I supposed to implement this?

can this be done using just one database?

thanks
-SelArom

r937 · #2 (**permalink**) 03-06-07, 06:30

Quote:

Originally Posted by SelArom

... but how can I map a specific post to the specific blog of a specific user?

one table for the blogs

Code:

create table blogs
( blog_id integer not null primary key 
, foo varchar(99)
);

one table for the users

Code:

create table users
( user_id integer not null primary key 
, bar varchar(99)
);

one table for which user(s) can post on which blog(s)

Code:

create table blogusers
( blog_id integer not null 
, user_id integer not null 
, primary key (blog_id,user_id)
);

and one table for the posts

Code:

create table posts
( post_id integer not null primary key 
, blog_id integer not null 
, user_id integer not null 
, foreign key (blog_id,user_id)
     references blogusers 
);

SelArom · #3 (**permalink**) 03-06-07, 10:30

this is pretty much exactly what I ended up doing, thanks i'm glad I wasn't off base. thanks for replying!

-SelArom

Internet Services

Web Development

Digital Marketing

Consumer Tech