Helpful Information

Category: MySQL

Article based website

What's the best way to structure a database for an article based website? I want to have multiple categories and multiple languages. Also, how do you do multiple page articles? Thank you for any help. All I need are abstract answers. I can develop it on my own.

Designing a database properly can take some time but it's really not too difficult and will save you time in the long run. This is what I do for a living. :)

The best way to start is by doing a brainstorm. Let's say I want to design a relational database for a video store. First thing to do is to think of ways to describe a video store and how it operates using nouns and then list them. For example:

customers
employees
dvd
vhs tape
games
prices
commissions

the list can go on. Once you have thought of everything you can start thinking how these nouns are related. Make sentences out of it for example:

Customers rent DVD's
Employees get commissions

Once you have come up with all possible relationships begin putting an ER diagram together. An Entity Relationship Diagram is a necessity for designing the database. The ER diagram essentially is the sketch of the structure of your database. In this case an Entity would be the nouns in your list. Those Entitys/Nouns become the tables in your database. The sentences that describe the relationship between the nouns become the relationships between the Entities.

To generate the attributes/columns of each table simply think about the noun and make a list of words that describe the noun. For examples:

What does a dvd have:

title
rating
category
year

These can become attributes of that entity.

The process is much more involved as this was just a simple and quick example. The best way is to read through some tutorials or buy a good book on database design. Here are some links:

http://www.islandnet.com/~tmc/html/articles/datamodl.htm

http://folkworm.ceri.memphis.edu/ew/SCHEMA_DOC/comparison/erd.htm

http://www.utexas.edu/cc/database/datamodeling/dm/erintro.html

http://www.surfermall.com/relational/lesson_1.htm

http://www.webmasterbase.com/article/378

There are many tutorials available so just do a search on

database design

data modelling

first of all, do not store languages at your db! all language switching do thru php script:
switch($lang) {
case 'lang01':
... do this
break;
case'lang02':
... do that
break;
...
}
just to simplify your queries.
next, separate tables for articles from themes:
theme [table]
idTheme ...,
theme ...,

articles [table]
articleID,
title,
description,
text,
date,
time,
source,
themeID,
pictureID,
userID,
...

pictureID if you want pictures with your articles (of course, create table to store PATH to pictures :), add (or remove) userID field if you want to keep tracks of your user actions (if several people have privileges to enter the articles)

hth

And as for languages, shouldn't I still make my articles table like this:

title_lang1
title_lang2
body_lang1
body_lang2
author
lang2_translator
etc?

Then use PHP to pick the appropriate ones?