Conceptual Design for PHP/MySql?

Hi

There are loads of database driven sites out there on the net that run on PHP/MySQL or similiar combinations.

I know such sites can be developed quite easily now, however, what I am more interested in is the actual design of such database driven sites.

Say for example you take the average computer hardware article/news site that runs on such a PHP/MySQL system, where articles are stored in a database and retrieved from there as needed.

What I would like peoples' comments on is a conceptual design for such a site.

What I have come up with so far is the following:

A conceptual diagram would consist of: Authors, Aritcles, Website, WebDevelopers (people who post updates to the site).

In a more schematic way:

ARTICLE(ArticleID, Title, PublishDate, Content)
AUTHOR(AuthorID, Name, StartDate)
TOPIC (ArticleID, AuthorID, Topic)

-----

I am still unsure, however, how this maps to a MySQL database for example.

Firstly, is my schema correct, if so.. do I just create tables with those properties?

Can someone please help as I know I can implement such a thing.. I just want to do a decent design!

Thanks!

Mak

Hi Mak,
You appear to be proposing three tables to cover a total of eight different fields. A "decent design" is one thing but simplicity is another. Why not a single table containing ArticleID, Title, PublishDate, Content, AuthorID, Name, Startdate, Topic ?
Selecting from a single, fairly small table is far less complicated for anyone not in the "guru" class (I am not in that class).
Keep it simple first, complicate it later.
Best regards, Chalky

hi mate,

i am not saying that i am a guru or anything! i just want to get to the third normal form as this is quite basic for any person wishing to design a simple database.

You could use the layout you described, it's really up to how you wish to code it. I mean there's no point in normalizing to the 3 (or more) level just for the sake of it. But then on the other hand, joining tables is fun...

So I would advice against using just one table for such a task...

As for the howto part, there are some tutorials available at this place that deals with the matter (creating tables, column types etc). Try those as they're explaining it quite good.

So a suggestion is to explore and examine some of the more popular news scripts that's floating around. You'll probably find some good examples there...

Good luck.

//NoXcuz

Hi again Mak,
Sorry if I've offended you. I had no intention of doing so. All I meant was that it's not a bad idea to keep things simple unless you've done a lot of it.

I'm afraid I don't understand what you mean by "third normal form". Would you be kind enough to enlighten me please?

Best regards, Chalky.

Hi there!

Don't worry! You have not offended me at all.. I welcome any kind of decent comments :-)

There is a topic called 'normalisation', or 'normalization' for our american pals. This consists of the 1st, 2nd and 3rd (as well as more) normal form which are used to 'normalise' the data and remove anomalies etc.

If you're interested.. have a look on the web for 'database normalisation' or something similiar.

Cheers.

Mak.

I think there are nine levels of db normalization for those who are interested (if any?). Though it's almost impossible to achieve that in real life without some extreme requirements...

//NoXcuz

don't suppose u have an online paper on them... or know of a site detailing 'em?

thanks.

Thanks Mak. I'll dig the book out and have a read.

All the best
Chalky

Cool dragon if you want to look up normilsation try looking at A-level + database/ computer science books they should cover it.

Isn't there six stages? One of them skipped getting a number somehwhere after three. I sort of stopped learning them once I got to the third.

AFAIK, here are the different levels of normalization:

1. First normal form
2. Second normal form
3. Third normal form
4. Boyce-Codd normal form
5. Fourth normal form
6. Fifth normal form (join-projection normal form)
7. Strong join-projection normal form
8. Over-strong join-projection normal form
9. Domain key normal form

Yeah, after stage three things get kinda complicated...

//NoXcuz

You know, I wonder if maybe you're coming at this from the wrong end. Perhaps the first question should be, what would the end user be coming to the site to get or do? Or, in other words, what do you want the end result of the database design to be? What is it supposed to DO for the user? When you know that, it might be easier to determine what's needed to make a database that does it.

Karen

Usually people stop at the third normal form. It's nice to have a system that has been extracted into separate entities but in this case I have to agree that if that's all you're having in the DB then you should consider leaving it as a single table. Yes this may mean repeating entities but the speed of searching and updating will be faster (though these days you would hardly tell it on a small DB) than on a relational DB. Of course if you're letting people update it via the web then 3rd normal form is best so that you can use tables as look-up tables.

You haven't got there though:

ARTICLE(ArticleID, Title, PublishDate, Content)
AUTHOR(AuthorID, Name, StartDate)
TOPIC (ArticleID, AuthorID, Topic)

I would do it as follows:

ARTICLE(ArticleID, TopicID, AuthorID, Title, PublishDate, Content)
AUTHOR(AuthorID, Name, StartDate)
TOPIC (TopicID, Topic)

Hi

I am actually quite close to what you got.. what I have is as follows:

-------

ARTICLES(articleID, relAuthorID, relTopicID, articleTitle, articleDate articleContent)

AUTHORS(authorID, relTopicID, authorFname, authorSname,
authorEmail, authorDate)

TOPICS(topicID, topicTitle)

-------

Now we have the primary key authorID from the Authors table related to the foreign key in the Articles table called relAuthorID. Thus, we can add another two hundred articles by the same author, for example, whilst still only inserting the author details once. The number of articles and authors can grow as large as we require without unnecessary duplication or corruption of data.

The primary key topicID from the Topics table is related to the foreign key in the Authors table called relTopicID. As a result, unnecessary duplication of data is prevented if, for example, we have two hundred articles of the exact same topic.

Furthermore, the primary key topicID from the Topics table is also related to the foreign key in the Articles table called relTopicID. This may seem like an unusual step as we already have Authors and Topics related and thus we can derive which article relates to which topic via a transitive dependency. However, the introduction of this relation, although strictly adding some redundancy to the model, is mainly for the ease-of-use it provides and also more meaningful querying when writing queries with SQL. I believe that this trade-off of some redundancy for a more meaningful and easier to manipulate model will pay dividends as the database increases in size or when, for example, new database administrators are hired.

-------

What do you think?

Cheers,

Mak.

p.s. Attached is a zip file of Word doc. showing my thoughts from zero to third normal form.

Now you're getting into the realms of referential integrety. Your design is good but I don't like the redundant use of the topicID. I shall copy your design for ease of referring to:

ARTICLES(articleID, relAuthorID, relTopicID, articleTitle, articleDate articleContent)

AUTHORS(authorID, relTopicID, authorFname, authorSname,
authorEmail, authorDate)

TOPICS(topicID, topicTitle)


The problem you have is that although searching may be made easier and sql commands simpler, you are actually making it more complexed. If somebody adds a new topic that affects records, you now have to update the AUTHORS and the ARTICLES tables instead of just the ARTICLES table. Also if you only have the author appear once in the AUTHORS table, and the author has a topicID then you have to make sure that any additional articles that author contributes has the same topicID. You are also limiting your authors to only having one topic. What happens if an author writes on two separate topics?