Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2005
    Posts
    20

    Question database schema for a multiuser/multiblog system

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 
    );
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2005
    Posts
    20
    this is pretty much exactly what I ended up doing, thanks i'm glad I wasn't off base. thanks for replying!

    -SelArom

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •