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?

fez · 14 Apr 2011 at 15:05

What are you building this in?

What rdms are you using?

You shouldn't really use uppercase table names due to the issue between operating systems and how they deal with table and field names.

How do you know where the images go in the page that you are referencing. At the moment you would end up with a load of images that relate to a page, but where abouts in the page do they sit.

For images you want a table that stores the name of the image along with any meta data that you may have for the image, you can then allow the user to browse images that have been uploaded and then add an img tag into the page/posts content.

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.

fez · 14 Apr 2011 at 15:27

With well structured css and html you shouldn't have to worry about the client buggering things up but I haven't found a cms that can allow the user complete freedom to create content as they wish without any though for the layout.

You can constrain the width of an image in the content to have it automatically scale to the correct height / width and using floats you can allow the user to enter images into the middle of their text and have it wrap nicely.

Are you building this from scratch or using a framework to build it around?

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?

djkav · 16 Apr 2011 at 16:38

I'd use look up tables for the page and post references to images. Take them out of the Images table as this is just describing a list of images.

Image Table - ImageID, Title, Source
Page Table - PageID,
Post Table - PostID, Introduction, main_content, MemberID

ImagesPages - ImageID, PageID
ImagesPosts - ImageID, PostID

Member Table - MemberID, Name, Alias, Username, Pass

That way your DB will be much quicker at lookups, also, you will know if you on a page or a post and use which ever is relevant.

So with either a PageID/PostID you will get a list of Images

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?

djkav · 16 Apr 2011 at 18:24

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

Nope, im working from the assumption that from your description that Pages and Posts are different entities.

Thus, if your working with a "Page" you only need the ImagesPages table. Likewise, when working with Posts you only need to reference ImagesPosts table. In your 1st example you said that either PageID or PostID would be NULL. Either way, the look up tables will be extremely quick as their only indexed keys in them.

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

djkav · 16 Apr 2011 at 19:36

Shicky said:
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

My error, I forgot the PKs

I added a PK due to the fact that a composite key from ImageID and PageID/PostID may not guarantee uniqueness.

ImagesPages - ImagesPagesID, ImageID, PageID
ImagesPosts - ImagesPostsID, ImageID, PostID

sist_si · 17 Apr 2011 at 09:54

personally i'd have a field in the images table to signify the source of the fk but if you're going strict 3nf that may not be valid. Also, it depends how big your db will get as to how this would perform. Anyway:

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

so, in your class (if you're doing this oo) you'd have to set the image parent_type before you submit to the db e.g. parent_type="post", parentID = 1
or
parent_type="page", parentID=1

that way, all the images are in 1 db table. no nulls.

OR - alternativley, you could have a "image_type" table like this:

Image Type Table - pk, image_type (e.g. "post", "page")

which you then store the "id" of the image type in the images table so:

Image Type Table - ImageTypeID, ImageType
Image Table - ImageID, Title, Source, ImageTypeID, parentID
Page Table - PageID,
Post Table - PostID, Introduction, main_content, MemberID

JonB · 17 Apr 2011 at 10:52

Don't forget createdDate and lastUpdatedDate for all tables. They can be useful later when trying to debug!

djkav · 17 Apr 2011 at 17:01

sist_si said:
OR - alternativley, you could have a "image_type" table like this:

Image Type Table - pk, image_type (e.g. "post", "page")

which you then store the "id" of the image type in the images table so:

Image Type Table - ImageTypeID, ImageType
Image Table - ImageID, Title, Source, ImageTypeID, parentID
Page Table - PageID,
Post Table - PostID, Introduction, main_content, MemberID

The problem with that is you haven't got an ImageID related to a PageID/PostID. Not that I can see anyways.

Also, its not good to have any PostID or PageID in the Images table as it will cause problems if say for example, the same page uses the same image twice.

The Images table should only describe an image. You need a lookup table to relate an ImageID to PostID or PageID.

djkav · 17 Apr 2011 at 18:35

This is how I'd do my schema:

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

Mine:
Members - MemberID, Name, Alias, Username, Paswword, CreatedBy, UpdatedBy, DateCreated, DateUpdated

Contents - ContentID, ContentTypeID, MemberID, Summary, Content, CreatedBy, UpdatedBy, DateCreated, DateUpdated

ContentTypes - ContentTypeID, Alias, Summary, CreatedBy, UpdatedBy, DateCreated, DateUpdated

Images - ImageID, Title, Source, CreatedBy, UpdatedBy, DateCreated, DateUpdated

ImagesContent - ImagesContentID, ImageID, ContentID, CreatedBy, UpdatedBy, DateCreated, DateUpdated

ContentTypeID = 1-Post / 2-Page

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?

djkav · 18 Apr 2011 at 18:55

I usually start by drafting out all the different sections and objects that will be required. Then I look at each one and see what kind of data I need to fulfil each role.

I then break that down into entities and begin to formulate a basic schema in 1NF (1st normal form).

From your last post, you seem to have areas that need consideration, that would probably have some impact on your schema