I am designing database by apply proper normalization. I know normalization decrease redundency.But at the time of data acess i have to use more and more joins. So i want to know which is better process design database in such a way that it avoids
joins(on the price of normalization) or apply normalization and use multiple joins at the time of data acess.
To be honest, the answer here is nothing. SQL Server, like any other RDBMS, couldn’t care less whether your data model follows any of the normal forms. You could create one table and store all of your data in one table or you can create a lot of little,
unrelated tables to store your data. SQL Server will support whatever you decide to do. The only limiting factor you might face is the maximum number of columns SQL Server supports for a table.
SQL Server does not force or enforce any rules that require you to create a database in any of the normal forms. You are able to mix and match any of the rules you need, but it is a good idea to try to normalize your database as much as possible when you are
designing it. People tend to spend a lot of time up front creating a normalized data model, but as soon as new columns or tables need to be added, they forget about the initial effort that was devoted to creating a nice clean model.
Advantages of normalization
1. Smaller database: By eliminating duplicate data, you will be able to reduce the overall size of the database.
2. Better performance:
a. Narrow tables: Having more fine-tuned tables allows your tables to have less columns and allows you to fit more records per data page.
b. Fewer indexes per table mean faster maintenance tasks such as index rebuilds.
c. Only join tables that you need.
Disadvantages of normalization
1. More tables to join: By spreading out your data into more tables, you increase the need to join tables.
2. Tables contain codes instead of real data: Repeated data is stored as codes rather than meaningful data. Therefore, there is always a need to go to the lookup table for the value.
3. Data model is difficult to query against: The data model is optimized for applications, not for ad hoc querying.
To be honest, the answer here is nothing. SQL Server, like any other RDBMS, couldn’t care less whether your data model follows any of the normal forms.
You could create one table and store all of your data in one table or you can create a lot of little, unrelated tables to store your data. SQL Server will support whatever you decide to do. The only limiting factor you might face is the maximum number of columns
SQL Server supports for a table.
SQL Server does not force or enforce any rules that require you to create a database in any of the normal forms. You are able to mix and match any of
the rules you need, but it is a good idea to try to normalize your database as much as possible when you are designing it. People tend to spend a lot of time up front creating a normalized data model, but as soon as new columns or tables need to be added,
they forget about the initial effort that was devoted to creating a nice clean model.
Advantages of normalization
1. Smaller database: By eliminating duplicate data, you will be able to reduce the overall size of the database.
2. Better performance:
a. Narrow tables: Having more fine-tuned tables allows your tables to have less columns and allows you to fit more records per data page.
b. Fewer indexes per table mean faster maintenance tasks such as index rebuilds.
c. Only join tables that you need.
1.More tables to join: By spreading out your data into more tables, you increase the need to join tables.
2. Tables contain codes instead of real data: Repeated data is stored as codes rather than meaningful data. Therefore, there is always a need to go to the lookup table for the value.
3. Data model is difficult to query against: The data model is optimized for applications, not for ad hoc querying.
vish02chouha...
Member
303 Points
251 Posts
Normalization results more joins is it efficient
Feb 27, 2013 06:25 AM|LINK
Hi,
I am designing database by apply proper normalization. I know normalization decrease redundency.But at the time of data acess i have to use more and more joins. So i want to know which is better process design database in such a way that it avoids joins(on the price of normalization) or apply normalization and use multiple joins at the time of data acess.
wmec
Contributor
6195 Points
3214 Posts
Re: Normalization results more joins is it efficient
Feb 27, 2013 06:35 AM|LINK
To be honest, the answer here is nothing. SQL Server, like any other RDBMS, couldn’t care less whether your data model follows any of the normal forms. You could create one table and store all of your data in one table or you can create a lot of little, unrelated tables to store your data. SQL Server will support whatever you decide to do. The only limiting factor you might face is the maximum number of columns SQL Server supports for a table.
SQL Server does not force or enforce any rules that require you to create a database in any of the normal forms. You are able to mix and match any of the rules you need, but it is a good idea to try to normalize your database as much as possible when you are designing it. People tend to spend a lot of time up front creating a normalized data model, but as soon as new columns or tables need to be added, they forget about the initial effort that was devoted to creating a nice clean model.
Advantages of normalization
1. Smaller database: By eliminating duplicate data, you will be able to reduce the overall size of the database.
2. Better performance:
a. Narrow tables: Having more fine-tuned tables allows your tables to have less columns and allows you to fit more records per data page.
b. Fewer indexes per table mean faster maintenance tasks such as index rebuilds.
c. Only join tables that you need.
Disadvantages of normalization
1. More tables to join: By spreading out your data into more tables, you increase the need to join tables.
2. Tables contain codes instead of real data: Repeated data is stored as codes rather than meaningful data. Therefore, there is always a need to go to the lookup table for the value.
3. Data model is difficult to query against: The data model is optimized for applications, not for ad hoc querying.
To be honest, the answer here is nothing. SQL Server, like any other RDBMS, couldn’t care less whether your data model follows any of the normal forms. You could create one table and store all of your data in one table or you can create a lot of little, unrelated tables to store your data. SQL Server will support whatever you decide to do. The only limiting factor you might face is the maximum number of columns SQL Server supports for a table.
SQL Server does not force or enforce any rules that require you to create a database in any of the normal forms. You are able to mix and match any of the rules you need, but it is a good idea to try to normalize your database as much as possible when you are designing it. People tend to spend a lot of time up front creating a normalized data model, but as soon as new columns or tables need to be added, they forget about the initial effort that was devoted to creating a nice clean model.
Advantages of normalization
1. Smaller database: By eliminating duplicate data, you will be able to reduce the overall size of the database.
2. Better performance:
a. Narrow tables: Having more fine-tuned tables allows your tables to have less columns and allows you to fit more records per data page.
b. Fewer indexes per table mean faster maintenance tasks such as index rebuilds.
c. Only join tables that you need.
Disadvantages of normalization
<div style="border: none; border-bottom: solid windowtext 1pt; padding: 0 0 1pt;">1. More tables to join: By spreading out your data into more tables, you increase the need to join tables.
</div>2. Tables contain codes instead of real data: Repeated data is stored as codes rather than meaningful data. Therefore, there is always a need to go to the lookup table for the value.
3. Data model is difficult to query against: The data model is optimized for applications, not for ad hoc querying.
HuaMin Chen
oned_gk
All-Star
31021 Points
6354 Posts
Re: Normalization results more joins is it efficient
Feb 27, 2013 09:13 AM|LINK
Pbalan.in
Contributor
2142 Points
483 Posts
Re: Normalization results more joins is it efficient
Mar 01, 2013 11:26 AM|LINK
Please read this below articles for understanding which is the best and when to use...
http://stackoverflow.com/questions/5773611/how-costly-are-joins-in-sql-and-or-whats-the-trade-off-between-performance-an
http://stackoverflow.com/questions/8232793/normalization-makes-joins-accross-multiple-tables-difficult
http://betaprogrammer.com/content/?p=218
http://www.ovaistariq.net/199/databases-normalization-or-denormalization-which-is-the-better-technique