a little normalization advice?

knickerlas

Hi all

I'm going to build a very basic news database, it will be one news item per page and has the following required fields

It seems clear to create two tables

id|title|desc|keywords

and

id|headline|subhead|text

but where should the date go? and does it mattter?

I'm tempted to put it in the first table as it will help if I want to search through the DB for keywords and dates but I'm looking for a second opinion.

Many thanks

Nick

Anon

Why does it seem clear to create 2 tables?

Creating a 1 to 1 relationship isn't a good idea it just adds overhead. There are very few reasons to create a 1 to 1 relationship and this isn't any of them.

The only reason to split this data into 2 tables is if any of the data in 1 table is going to relate to multiple records in the other table.

Anon

Nick,

Normalisation and DB design depends very much on what your intentions are for the database and how you might see the applications and DB scaling up in the future.

From the fact that you say this is a basic news site, I don't see a reason to carry out the split you suggest. Splitting into two tables will result in you having to do a join of the two, each time you work with the all that data. I don't also see a logical 1:M or optionality relationship between the two tables you suggest.

There has to be a compromise between textbook normalisation and efficient design. By efficient design, I don't just mean efficient query-wise, but also efficient in terms of ability to create applications and queries quickly. It can become painful to have to reunderstand joins and have to write join SQL.

The only obvious split in your table would be suggested by the plural in your "keywords". You will be duplicating words in this field, and keyword:article will be a M:M relationship so you could do this:

keyword_id|keyword
keyword_id|article_id

But you'll have to decide whether you'll really be able to be bothered to code all those joins. I'd stick with keywords in one field and just do "like '%word%'" queries on it if you need to.

There is nothing wrong with choosing not to normalise your tables if there are no scalability issues and if there is no obvious relationship.

KISS - Keep it short/sweet and simple

anthony

knickerlas

KISS is a sales thing isn't it?

Okay, I've seen the light guys!

The reason I was opting for the split (apart from my clear inexperience) was that I was told that grouping related data was a good idea.

However, after seeing your posts it seems rather silly to split so thanks very much for the pointer:

Nick

Anon

Nick,

Grouping related data is definatly a good thing, but in this case all of your data is related.

Anthony described it so much better than I did but here are a couple of rules that I use when I start designing tables:

Is all of the data that I want to store for this item always going to be used? In other words for this table will I ever have any blank columns (blank columns = inefficiancy since most RDBMS will pull data based on the size of the row and will pull 10 characters for a 10 char column even if that column is empty.)
Is there any logical way to attach these "blank" columns? I look for any 1:M relationships that could be used (like Anthony suggested with your keywords).
Are these 1:M's going to save me more than they cost me? Again like Anthony showed while having a table of keywords would allow you to re-use the words in multiple articles is it going to cost you more in query/programming time to utilize this?

Once I've done this for my table I may have additional tables that I need to do this for to make them work properly, so I just start over with those.

Normalization is a good thing, but like most ideals it's very hard to live up to.

Anon

Cheers Rob

This post has been a real eye opener for me. I guess like most people that love programming/designing I'm a bit of a purist at heart. However, I'm not stupid and those subjects I'm a little more profficient in regularly get this kind of treatment.

So all that you and Anthoney makes sense and will definately help in my mission to master SQL!

Once again mucho grassy arse.

Nick

kidestranged

Just to add my two cents:
An example of when when you would want to add another table is if you were to include the publisher. Let's say the fields would be title, contact name, phone and web address. It you were to include these fields in the news story table, every time you wrote a story for Readers Digest, the table would have to store all the contact information over and over again. This is when related tables would make sense.

[deleted]

Uhm... the 'proper' rules for normalization are based on quite different criteria.

The fact that some data is not always used is not one of them. For ex: if you have an address book app, then you might want to list only the names here, and the names plus addresses there. Some people have mobile phones, others do not. Does that mean I'd have to split the addresses and mobile-phone fields from the main table? No it doesn't 🙂

If you don't use a field in a table, you leave it out of your SELECT statement, and the database will happily skip it.
You example of 'the database always reads X characters, even if they are all blank' just makes that better, because the database already knows how many bytes to skip.
And if you use an index, the database can jump straight to the parts it does need, coimpletely ignoring whatever is 'not used'.

The only reasons for normalization are to prevent redundant data, to make the tables sane, and to enforce data consistency.

That means, they contain no data that is already stored elsewhere, they contain no data that is a product of other data already in the same record (derived data), and 'shared' data always keeps the same value. (if the options for the content change, they change wherever the option is used)

For example, if I have a news item, this item must be written by someone.
Fastest method: add a column to hold the actual author's name.
Now the author changes his name (for whatever reason) Now you have to manually go through your database and change the old name to the new name wherever the old name was used. If the design is normalized, all the authors are listed in a seperate table, and the news item only points to authorid=2.
Changing the name of the author with id=2 automatically changes the author's name for all the newsitems wrote.

Normalized tables are not always the fastest, but in the long run, they are the ones that you have the fewest problems with.
Because a normalized design must be normalized. If you don't normalize in the database, you will have to do it in the script, and that is a lot more difficult.

Anon

I know that the way I design tables/databases dosn't follow the rules of normalization. But like I said following all of the normalization rules can be quite a headache and cause alot of slowdown. Sometimes you just have to sacrifice that for speed, especially in a web environment.

And what I've found in my testing is that the database doesn't "skip" the fields you don't include in your select, it just doesn't return them to you. It still reads them from the disk, so if you have huge tables then you could end up accessing the disk alot more for small queries because the database is pulling in the entire record and then just giving you the requested data back.

But I agree that normalizing a database is a good thing, and I try to normalize my data as best as possible, but sometimes you just have to wing it for performance.

[deleted]

As with my discussion with dmitry, this is only a discussion/exchange of opinion. No offence intended, don't take this the wrong way etc etc...

"and cause alot of slowdown. Sometimes you just have to sacrifice that for speed, especially in a web environment."

When is the last time you created a query that ran in more than 0.01 second? :-)

"And what I've found in my testing is that the database doesn't "skip" the fields you don't include in your select, it just doesn't return them to you"

Which database did you test, and how on earth did you find out that it did not skip those few bytes? :-)

"It still reads them from the disk, so if you have huge tables then you could end up accessing the disk alot more for small queries because the database is pulling in the entire record and then just giving you the requested data back."

So basically you want to remove the 'not always used' fields to make the tables smaller? that's nonsense.
We're talking maybe 32 bytes per record,
even at 10 million rows that's only 32 Megabytes, and my harddisks read over 60 Megabytes per second.

"but sometimes you just have to wing it for performance."

correction: sometimes you just have to take the slowdown for granted, or suffer the consequences.

In the end, the database design requires normalization, and if you don't do it in the database, you'll have to do it in the script, and when you do it in the script, you are on your way to get into big trouble.

:-)

Anon

Don't worry I didn't take any offence. I was just expressing my oppinion of what I do and what I've seen happening.

I've seen the database slowdown on queries with large tables/amounts of data in a table on both Oracle and Informix. My oppinions may come from the fact that I'm a dinosaur and like programs to run a effeciently as possible.

From what I've seen both Oracle and Informix (and probably PostgreSQL given it's and Informix's roots) pull records for you query into memory and then work on them from there to give you the data that was requested. If you have a blob field in your table, but you aren't requesting that data it's still pulled into this memory area and worked with to get you your dataset, and the pulling in of this field can cause a major impact forcing the database to read from the disk multiple times to get the full result set. Now if your blob field was in it's own table then your query could access the disk only once. I admit that these are fairly specific conditions, but I've seen it happen. And when you are working on a large system (10000's of simultanious users) going from 0.02 to 0.01 is a big deal. But it's probably not a very common occurance for most web sites, but it is something that I keep in mind when designing.

I do agree fully that you want to do your normalization work in the database and not in the script since typically the database will run faster than the script.

[deleted]

"My oppinions may come from the fact that I'm a dinosaur and like programs to run a effeciently as possible"

grin I am exactly the opposite. I feel that efficient is good, but consistancy, reliablility and scalability are better.

(which is why I save so much fun kicking against MySQL: all speed, no consitancy, not enough reliability, and... well... uhm.. scalability?)

Anon

"grin I am exactly the opposite. I feel that efficient is good, but consistancy, reliablility and scalability are better."

"(which is why I save so much fun kicking against MySQL: all speed, no consitancy, not enough reliability, and... well... uhm.. scalability?)"

You Thrill Seeker.

[deleted]

My name is Edge, 'livin on the' Edge