Database design question for linking or tracking

[uniguyUK]

Hi everyone,

I have a question. I am making a system that lets the user build web pages much like a custom CMS to learn about SQL, PHP etc.

Anyway.. I have a login page and the user is allowed to click create new site. This then asks how many pages they want to create. It also asks the user to type the titles and paste in the content (text) The user can currently choose up to 5 pages per new site. But my question is, how would I design the database on phpmyadmin for this.

I need it linked so the system knows what pages are for a particular website and also where to place the content in the templates when it all comes together. I currently have been thinking the following fields but all doesn't add up and I think I am missing something simple. Bear with me it's my 1st time on databases :)

websiteID, WebsiteTitle, noPages, pageID, pageTitles, pageTitleID, content, contentID.

The websiteID will be A/I so it is unique everytime a new website is created but my problem is that a user can click create site, then they have the option to create up to 5 pages which obviously all cant have the same pageID so how would I link all of this in??

Sorry for anyone that is database gurus, I apologise, I am only an amateur at the database side of things and hope to get up to speed soon and hopefully this project is a good way to start.

Thanks for your patience

[uniguyUK]

Update, I have been doing some reading and have now came up with this structure. Could anyone tell me if it is right?

5 tables as follows:

1) website with fields (websiteID), (Name)
2) page with fields (PageID)
3) titles with fields (TitleID, title)
4) content with fields (contentID, content)
5) link with fields (websiteID, pageID, contentID, titleID)

Hopefully this is more like the structure I need. I think this could work but would love to hear from someone who knows better about this than me.

[BitBlt]

Just so you know, the idea of "right" is sort of amorphous. If your data structure stores your data the way you need it then it can be considered "right enough".

If you want to know whether your data structure follows a specific set of rules, such as "normalization", that's a different question.
</nitpicking>

Personally, I prefer fully normalized structures. It's a bit of overkill for a lot of applications, but it does open the door to expanding the structure if you decide to add features later.

Given that, here's some advice:
1. A page can appear on only one website, so there should be a direct non-identifying relationship from Website to Page.
2. A title applies to only one page, so it should either be an attribute of page, or a child of Page (if there is more than one Title per Page).
3. Content can appear on only one page, so it should either be an attribute of Page, or a child of Page (if there is more than one Content per Page).
4. Link table should disappear. It is not needed.

You should wind up with at most 4 tables, with relationships.
Website---->Page
Page---->Title
Page---->Content

Hope this helps. Good luck!

[uniguyUK]

This is excellent, thanks. Yes I would like to have it fully normalised just in case I would need to expand later. Thanks for this, it's a great help :)