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.