Database Design for Private Dev. Blog

[Xdega]

Hey.

I am completely new to this. But I just started working on some very basic database design for a small "starter" project in prep for my IT (Web/Database) Associates college classes. I want to follow procedures used in the industry that involve careful planning of databases prior to any coding. Although I am leaning more towards the coding side of things in which I want to focus. (sidenote: To clarify, is my chosen major still appropriate for a career in web based coding?)

 

Anyways....

My project is simply a "dev notes" private blog of sorts for me and maybe some others to share information posts (the "post data" I will be able to experiment with things like custom BBcode, sessions and maybe even a rudimentary WYSIWYG editor. I want to include features like avatars for the sake of learning and aesthetics, and the option to upload files to a pre-defined directory.

 

I Originally began with a separate table for avatars only, then spent the entire morning realizing that this probably wasn't necessary due to wanting to implement this as a simple url link from a imageshack/photobucket upload (the image can be cropped/resized via coding).

 

Here is what I have so far for a basic database design after playing around with the Model Editor on MySQL Workbench:

 

Are there any glaring issues that can be noted with the above design?

 

Thanks.

 

 

[ignace]

The username in post_data should be user_id and a reference to users (user_id). Choose your data types more appropriately. Do you seriously need space for 2 billion users? If your password is hashed you'll probably want something like CHAR(32) or CHAR(40). For e-mail address VARCHAR(96) should suffice and avatar_url VARCHAR(128), date & time should be combined in a more descriptive column: posted_at DATETIME post_data should be TEXT or if you are running MySQL 5.0.3+ you can declare VARCHAR(512) for example if you need that many characters.

 

Your database design clearly hasn't been thought through as many of the things you mentioned aren't modeled in it.

[Xdega]

Thanks for the response and suggestions on the changes.

 

I did originally have a user_id reference, I then decided to go with "username" instead, since I have a post_id as a unique identifier and figured that user_id would just require more dependence on the users table? I was thinking along the lines of the post_data table working independently. Although I now notice that I would still rely on the "users" table for the Avatar reference.

 

You said that a lot of the things mentioned were not modeled in, are you referring to BBCode? (plan to accomplish this via regex with the information stored in "post_data") or file uploads? (I plan to just have all files uploaded to x directory then using code just list the contents of that directory).

Cant for the life of my figure out what else I need to store in the database /shrug

any ideas?

 

I know it looks like I have spent 0 time trying to plan this out, but believe me I have. I just have no practical experience with this, so kinda my first crack if you will. Although I have read a few articles/guides. 

 

Thanks again for the direction.

[ignace]

I did originally have a user_id reference, I then decided to go with "username" instead, since I have a post_id as a unique identifier and figured that user_id would just require more dependence on the users table? I was thinking along the lines of the post_data table working independently. Although I now notice that I would still rely on the "users" table for the Avatar reference.

 

Why is it bad to dependent upon another table? This isn't OO in fact dependencies between tables is a good thing, it's called: data integrity and makes sure no invalid data is entered in your database.

 

Look on other blogs for inspiration, you are obviously missing categories (1 category, many posts) and tags (many tags, many posts). If people are uploading stuff you probably want to know who uploaded what? People can comment on your posts, even comment on comments. Who created the post, who edited it, who is currently editing it, .. enough stuff to think about.

 

Did you get a course into normalization? Sit down, put everything in one table, find repeating groups, and remove anomalies.

[Xdega]

Ah! Ok. I get what you are saying now.

 

Yeah clearly these extra missing categories I was just not even thinking about and completely overlooked.

I am not starting classes just yet, I plan to get the financial aid sorted and get in to classes either Spring or Summer qtr of this year.

 

This is pre-study, because clearly I have a LOT to learn. Hopefully the courses I take will bring me to the basics and take me step by step in the correct process, in the meantime I think I am going to have to take my time with this and brainstorm harder while maybe working on some simpler less DB intensive projects as I work more with PHP coding (which I enjoy more than the actual design of the databases).

 

Also, do you have any personal recommendations for blogs on this topic?

Thanks again.

 

ps: I'm gonna be honest though, I was semi-rushing this step as I wanna sit down and start writing some code

 

[ignace]

I'm gonna be honest though, I was semi-rushing this step as I wanna sit down and start writing some code

 

You don't have to do a full project to write code. If you want to use a framework for your blog, you can experiment with it. If not, you can get familiar with the different functions in PHP and write your own set of functions (or objects if you are going OO) that you know you will need in your application. If you are planning on allowing people to comment on comments it may be wise to write some function that layers them below one another. Quite possibly you also want to format your dates to a given format, so you'll need a function for that too.

[Anti-Moronic]

Trust me, do this stuff first! PHP is childs play compared to effective database design.

 

'Normalisation' is wholly encouraged so get used to managing lots of table dependencies in the future.

 

The most immediate problem with using username here as a kind of foreign key is that if you change the username, you now how to change the username in the child tables. It's horrible design. Use the user_id.

 

You might want to use the other visual aids workbench has to better design and visualize the database and relationships.

 

I'd definitely advise you look into JOIN queries and such, and put together a small database - maybe 5 tables all holding a relationship - and practice with that. Jump into the deep end and take a look at osCommerce or ZenCart. Buy a sql book and a book about database design.