Database design help

Shicky · 14 Apr 2011 at 14:50

Long story short Im trying to figure out the best way to structure a database for my website. My plan was to essentially show me bringing it into third normal form, therefore showing a correct database design approach. My current plan was to have:

Image Table - ImageID, Title, Source, PageID, PostID
Page Table - PageID,
Post Table - PostID, Introduction, main_content, MemberID
Member Table - MemberID, Name, Alias, Username, Pass

As you can see im not entirely sure what way to go. If I have a pageid and postid as foreign keys in the image table, I assume this invalidates 3NF? As one of these values will need to be null everytime. I cant really lump pages and posts together as they're different things. The pages being about.php etc, posts being news posts/blog entries in style.

Anybody have any ideas how to structure they above? I was considering a section table of some description, then you could attach orders to the table so the sections appear in the correct order on the page?

Shicky · 14 Apr 2011 at 15:23

Using MySQL through phpadmin to handle the DB. I never use uppercase table names out of habit, didn't even realise there was a proper reason to do so! The above I just typed into the post to show how I was thinking of things.

I was going to use SQL queries in order to place the images in certain positions, with a forced height and width to try and ensure styling issues dont occur. For text content I was basically going to do the layout hardcoded into the html with bits of php/sql queries used to get a display content from the db itself.

This way my client will be able to edit the actual content but their changes shouldnt completely destroy the structure/styling of a page.

Shicky · 14 Apr 2011 at 15:48

Building from scratch, its my uni final year project to build a webby and cms for these ppl, hoping to do a bit extra and examine their stream (as its a radio station.)

I basically just rapid prototyped and was happy with where I got so integrated the design to the prototype CMS and im just continuing to develop from there. Unfortunately the client has only recently said they want control of the pages themselves now too in terms of content, my original plan was to hard-code that all essentially and they would have freedom over posts/blog/news entries (whatever u want to call it!)

So thats why the current database headache is going on. Not too sure how to accomodate the pages. Thinking having a section table, and basically allow one image and a certain amount of content on each section. could use javascript to enable expansion for further sections. Then obviously use the pageID as a foreign key in that table

Shicky · 16 Apr 2011 at 14:33

Is it okay to end up with my DB having the two foreign keys situation then? with one of the two being null each time? That means its in 2nd normal form, right?

Shicky · 16 Apr 2011 at 17:03

mmm never remotely thought about that. So in the imagesPosts/pages tables both are used together to form a composite key?

Shicky · 16 Apr 2011 at 18:40

Yes they're entirely different entities. I just meant in terms of the ImagesPosts table, the imageID and the postID would be used together to form a composite key?

I like the idea for sure but need to check what form it would be valid in then etc as im pretty unsure myself, its been a while since doing all that crap

Shicky · 18 Apr 2011 at 14:36

Good bit different from suggestions I think but might need to adopt an approach like has been suggested. My way im not sure if the lookup tables are needed? Or should I do this anyway to increase speed etc?

Members - ID, alias, username, password, permission_level, date_created, date_updated

Images - ID, title, source, date_created, date_updated

Pages - ID, name, date_created, date_updated

Sections - ID, page_ID, image_ID, content, date_created, date_updated

Posts - ID, image_ID, Intro, main_content, member_ID, date_created, date_updated

Perhaps decent explanation will help. Member & image I imagine are easy enough. Pages are just that, the pages of a website, so each one will correlate to a page of the site - home, about, contact. Each of these pages will be broken down by sections, therefore each section will exist on a page and a page can have numerous sections, thereby making up its content. I'll limit each page to 5 sections and images I reckon via javascript. The posts then act as the active content, in a 'blog' manner really. A user can create a post, on the index page this will show the 3 most recent posts, including the image and the intro section. the user can then click on a link to see the full story, including the main_content section. This will also be able to be accessed via a news archive page which will be similar to the 'blog' style on the index page but allow five results/news posts per page. This is starting to feel a little more daunting than I had thought

One problem is however I have several 'custom' modules I'll want the user to be able to edit. such as a list of top tunes (simple list of 10 items), images for a slideshow on the main page(jquery) and adverts. Im not sure how to add this into the DB schema, just a new table for each?