normalize

hi,

can somebody gives me an example
of denormalize improve performance.
i am not familar with that.

sometimes if you have to use join to combine two or more columes, you can move one not frequently changed column to another table to decrease or eliminate join. this improve proformance. but you should be careful.

suppose you have devloped a library mamnagement system
with the folowing tables which are normlaized.with the folowing relations,
member can borrow many books and books can be borrowed by many members additionaly a book belongs to a particular subject (hope this is plain english)

table name(col1,col2.........)
members(mem-id,name,..........)
borrow (mem-id,book-code,date-borrowed,city,zip-code)
books(book-code,desc,sub-code.....)
subjects(sub-code,desc,)

if you want to know which members has borrowed which books on which subjects you have to do a 4 table join.which should look something like this
select distinct a.name,d.desc
from membes a,borrow b,books c,subjects d
where
a.mem-id=b.mem-id and
b.book-code=c.book-code and
c.sub-code=d.sub-code

ah now thats quite a gew joins isnt it ?
now how about breaking some rules of normalization or denormalizing as u call it.

lets change the dat model so that subject table is eliminated .
so we are left with the following tables
members(....)
borrow(....)
books(book-code,desc,subject)
in order to answer our previous question of you want to know which members has borrowed which books on which subjects
we would have to write

select distinct a.name,c.subject
from members a,borrow b,books v
where
a.mem-id=b.mem-id and
b.book-code=c.book-code;
you can get an answer in just 3 table join.
isin that great :-)

be warned denormalization has got its cost in case of real applications like the one i work upon i would probably ahve to code additional database triggers if i dont normalize to maintain refrential integrity.

hope this helps


hrishikesh

adding to what i said on a serious note you acn have alook at denormalization in by running the script sh_mail.sql which creates the datawarehouse schema and the following atbles

Table Number of Rows
customers 50.000
countries 19
products 10.000
sales 1.016.271
times 1461
promotions 500
channels 5

script is supplied with oarcle 9i (i am not sure about 8i though)

hope this helps

hrishikesh

A simple example is the zip code/state and city relationship. Rarely do you see tables where an address is stored with just a street, number and zip, and then a lookup table with city/state. However, you can determine the city/state from the zip. It's just easier to record it every time, and most people denormalize to avoid the operational, programmatic and database complexity and maintenance headaches of normalizing this relationship.