Webmaster 101

To keep related data together, the best way to set up an organized database is by setting up multiple tables. For e.g. in a news site, we keep news with news separated from authors, blogs separated from news. It is easy because then it is easy and quicker for the database to locate the related reference and also the other references that might just be useful.

If the data is not separated, then we would keep the author information with the news and the blogs would be separated, thus the whole system gets very susceptible to errors. And also then every time there is an edition, we will have to edit it everywhere that it is used.

Database normalization is when the data is kept separate, like this, and is quite complicated in its own right.
the previous CMS table is slightly modified here to fit the news:-

CREATE TABLE newsitem (
newsid INT PRIMARY KEY,
newstitle VARCHAR(255),
newscontent TEXT,
authorid INT REFERENCES authors(authorid)
);

the author’s table is created first because the ‘newsitem’ table will make a reference to it and the database should be able to check to make sure it’s available so that the relationship between them can be established.

The way that we get the authors address and name for a specific news report is to when we join the tables of authors and news together, they will both have a primary key so that he link can be established, just like in the above example it is ‘authorid’. The primary key will help establish the uniqueness of each row and also will join them together as a table.

Whenever the tables are joined in a question or a search, the primary keys need to be used so that each row is uniquely and positively identified and thus there is no scope of errors.

Filed under: Databases