Page 1 of 1

Planning a Blog Database

Posted: Sat Oct 25, 2008 3:37 pm
by JAB Creations
I feel I'm far along enough right now to plan a blog database. I'm looking for input on designing the database which I've already put some thought in to. The blog will be based off the project I have worked on as a test for a recent job interview.

I'm approaching the blog to be as open as possible. In example I eventually plan to write my own forum software. Therefore I'm trying to take an approach of each blog post as a thread. Each thread is started by he blog author (me). I am also interested in thread permissions. Keep in mind when I say thread I mean an individual blog post.

Here is the my first MySQL table which I'm rewriting from scratch from the previous builds (that were called "gamer") now to be known as "blog"...
id (int/10)
thread_author (VARCHAR/128)
thread_body (VARCHAR/65536)
thread_category_id (int/10)
thread_date (VARCHAR/19)
thread_permissions (int/1)
thread_title (VARCHAR/128)
thread_url (VARCHAR/128)

The id will be set to auto_increment (does or should I set this to PRIMARY?).

The thread_author could be author_id in a relational table and I'm debating that in my head. I don't plan on allowing people to change their aliases. I'm definitely open to input about that as I am interested in scaling this software in the future. An example of something that annoys me to no end: people who randomly and uselessly waste aliases; there is a fake "jabcreations" on MySpace so if the roles were reversed I'd consider it only fair to reassign aliases in this situation. This will likely end up linking to a relational table.

The thread_body will be the XHTML data specific to the blog thread's first "post".

The thread_category_id will be the category_id associated with the thread. In example "PHP" might have an ID of "7" in the category table so this will likely end up linking to a relational table.

Date is a given.

The thread_permissions will use an integer value to determine what level is required to view the thread. I've long thought this out so here is what I currently have as far as permissions go...
9 - Admin (me)
8 - Admin assistants?
7 - Moderators
6 - Premium Members
5 - Members
4 - Frozen Members (Only Admins can delete accounts, mods can only temporarily freeze)
3 - Unverified Registered Users
2 - Unregistered Users
1 - Bots
0 - Banned

...however I could end up using letters (u for unpublished in example) so I'm open to suggestions in that regards.

I'm not sure if I should use title or subject? Title seems most appropriate to me right now.

The URL is statically created so PHP won't have to always replace spaces with underscores and make everything lower case all the time. Since a database query is going to be made any way I see it as a way to help offset having to search for the thread associated with the requested URL (versus having URL's using table id's which is not as SEO friendly). If someone has a constructive alternative I'm open to suggestions.

I'm also interested in any columns I may need. Additionally is there anything that could be related to other parts of the database? I will be implementing this with my current private message system and will most likely revise that database before implementing it all together. I'm looking to seamlessly integrate private messaging, blog, forums, etc in to one in-house solution so users at my site won't have to registered more than once.

I'm going to read about database types more and try to determine if there is anything else I'll want to include as far as the main thread table is concerned. I look forward to replies. :mrgreen:

Re: Planning a Blog Database

Posted: Wed Oct 29, 2008 9:24 am
by playgames
wordpress is a good exp. And her database
.
But is someone(little blog) who don't want the so fat blog(wordpress).

I think 1 tables is enough:

blog article

create table blg_article(
aid int(10) unsigned not null auto_increment,
art_title varchar(128) not null default '',
art_summary varchar(255) not null default '',
art_body text not null,
art_postdate int(10) unsigned not null default 0,
art_lastmdate int(10) unsigned not null default 0,
tags varchar(255) not null default '',
flg tinyint(2) unsigned not null default 0,
#and more if you need.......
)engine=myisam default charset=utf-8;

Re: Planning a Blog Database

Posted: Wed Oct 29, 2008 9:33 am
by onion2k
JAB Creations wrote:Here is the my first MySQL table which I'm rewriting from scratch from the previous builds (that were called "gamer") now to be known as "blog"...
id (int/10)
thread_author (VARCHAR/128)
thread_body (VARCHAR/65536)
thread_category_id (int/10)
thread_date (VARCHAR/19)
thread_permissions (int/1)
thread_title (VARCHAR/128)
thread_url (VARCHAR/128)
thread_body should definitely be a TEXT() type rather than a really big VARCHAR().

I'm not sure I like thread_category_id. It limits posts to only being in 1 category. Why not use something like the game category cross reference table you worked out before?

Re: Planning a Blog Database

Posted: Wed Nov 05, 2008 12:51 pm
by JAB Creations

Code: Select all

SELECT thread_title, thread_urlFROM xhref_tagsLEFT JOIN threadsON xhref_thread_id = thread_idWHERE xhref_tag_id = '3'ORDER BY thread_title ASC
thread_title thread_url
Example 3 example_3
Hello World! hello_world
Quick Sample quick_sample

Code: Select all

SELECT thread_title, thread_urlFROM xhref_tagsLEFT JOIN threadsON xhref_thread_idWHERE xhref_tag_id = '3'ORDER BY thread_title ASC
thread_title thread_url
Example 3 example_3
Example 3 example_3
Example 3 example_3
Example 4 example_4
Example 4 example_4
Example 4 example_4
Hello World! hello_world
Hello World! hello_world
Hello World! hello_world
Quick Sample quick_sample
Quick Sample quick_sample
Quick Sample quick_sample