| Author |
Topic |
|
karuna
Aged Yak Warrior
582 Posts |
 Posted - 2005-03-18 : 04:53:45
|
| Hi,This is with reference to my recent threads in the forums.I'm designing the database for the web application, The initial database dump from the client will be half a million records, and every month the data dump would be around 50,000 records.The infrastructure they talk about is a 2 Clustered Servers running Sql and a possible SAN in between them. The servers are 2x 1Ghz processors512 MB RAMPrimary Hard Disk: 40GBSecondary Hard Disk: 40GBI'm in a dilemma whether should I go for the normalization during my db design or not?Karunakaran |
|
|
gpl
Posting Yak Master
195 Posts |
Posted - 2005-03-18 : 05:06:40
|
| KarunakaranIf your database is purely for reporting, then denormalising the structure gives you a performance boost, as you will not need to make all of the joins.Having said that, if you are doing complex queries, then the structure that you use should be able to support those queries without too much overhead. Again a read-only database allows you to put many indexes on the data to speed up retrieval.Does this help ?Graham |
 |
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2005-03-18 : 06:02:07
|
| Hello Graham,The application is kind of 75% on Reporting and other 25% on user interaction. Thats my bothering now, I suggested a denormalized structure for the performance, client is still evaluating on that. Mean while I thought to check out over here.Karunakaran |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-03-18 : 06:06:11
|
| You should go for normalization, and put appropriate indexes on the tables.It is not true that a denormalized database gives better perf. Maybe for some specific queries, but for many others a denormalized db will be worse.If you have cases where a denormalized table would give you a perf boost, you could consider an indexed view as well.rockmoose |
|
|
|
PW
Yak Posting Veteran
95 Posts |
Posted - 2005-03-18 : 11:01:48
|
| It is not true that a denormalized database gives better performance.It is not true that a normalized database gives better performance.What is true is that either may give better performance in a particular situation based on many factors. None of us have enough detailed info on *this* particular situation to give a conclusive answer.If normalization never improves performance, then I guess 1000's of dimensional modellers and Kimball-style data warehouse architects/implementors should be kicked out of their jobs immediately. |
|
|
|
Blastrix
Posting Yak Master
208 Posts |
Posted - 2005-03-18 : 12:07:00
|
| PW is correct. This is something that Karuna has to figure out. I think the best approach though is to normalize, and index properly first. If you find that you have performance issues, then start denormalizing as needed. It's much better to have the data already normalized, than to try and normalize everything after the fact many times.Steve |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-03-18 : 13:37:16
|
| Well you can't do either well if you don't first have a fully flushed out Logical Normalized Model....rules you need rules...Brett8-) |
|
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2005-03-18 : 23:45:40
|
| Hi All,Now I have a normalized db design, yet to have an approval from the client. I think it will be quite some time to get all the datadump from the client, we might get a little data to work with for the development purpose.Which in my opinion might not show any performance issues. So, I'm thinking of also having a other solution with de-normalized structure so that once I get all data dumps(I dont expect that from client before April) from the client I can check with both of options.Will keep you all posted.Karunakaran |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-03-19 : 02:41:51
|
quote:
I suggested a denormalized structure for the performance, client is still evaluating on that.
My biggest concern is actually keeping the client. If they know what normalisation REALLY means, you are out of a jobDavidMA front-end is something that tries to violate a back-end. |
|
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2005-03-19 : 02:51:56
|
| Hi David,Ofcourse they do know what normalization is, I have made my design normalized eventhough I wont say it as a completely normalized one. Now looks like there will an entire change in the structure of datadump the client is going to provide. Depending upon that I have to redo my work.I wish I could give the sample data and the current db design for review, but....Karunakaran |
|
|
|
PW
Yak Posting Veteran
95 Posts |
Posted - 2005-03-19 : 03:08:03
|
| >>If they know what normalisation REALLY means, you are out of a jobReally ?So if they have reports that require data elements from 15+ table joins out of their operational OLTP system, will s/he be out of a job if a replicated, denormalized star-schema of the data for reporting purposes reduces report run times by a factor of 10 or more and doesn't lock the operational system ?Been doing this since Sybase 4.x over a decade ago, and have never been out of a job for providing solutions that allow fully normalized OLTP systems to do what they're best at, while providing timely intelligence from the OLTP data via replicated, denormalized data.>>you are out of a jobGuess I just got lucky for a decade, huh ? |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-03-19 : 04:02:41
|
| >> Really?Yes. Really.>>.. replicated, star-schema, OLTP, 15+ table join..Is it possible that you are confusing the logical aspect of normalisation with the phyiscal implementation of it?>>Guess I just got lucky for a decade, huh ?"Guess I just go lucky in my millennia spanning career"...sounds so much better.DavidMA front-end is something that tries to violate a back-end. |
|
|
|
|
Normalization or Not???