Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

Using Denomalized Table in a application

I am designing the data model for na application. In the data model I have introduced a denormalized table which would store the static values, like State, Country, File Type, Status etc. This table has columns namely 'Type', 'Name', 'Value' and 'Parent Name'. This table will be used across the application to get data for Drop down etc.
The application that is being developed is on .NET framework and we will using stored Procedures.

We have another technical developer who does not believe in the concepts of having a denormalized table. He feels we should have seperate table for all value types ie, separate table for State, Country, File type, status etc and link them up together.

I want a second opinion on the best approach to the design the pros and cons of each of these approaches


Static Value table Structure
 
Column Name		Description
 
PARENT_ID		         Parent Type Row Id
TYPE		         Type
NAME	  	 	Drill Down Values or Name
VALUE_1		         Supporting Values
VALUE_2		
VALUE_3		
 
Sample Static Values
Address Type	Primary Address
Address Type	Home Address
Address Type	Residential Address
Address Type	Mailing Address
Address Type	Deliver To Address
File Status 	Pending
File Status 	Withdrawn
File Status 	Rejected
 
 
 
 
Sample Static Value Example

Open in new window

0
sburman
Asked:
sburman
4 Solutions
 
chapmandewCommented:
Listen to your technical developer...and read this article.

http://www.simple-talk.com/opinion/opinion-pieces/bad-carma/ 
0
 
PedroCGDCommented:
Dear sburman,
It all depends one the several factores, and for that reason I suggest to read the SQLBI methodology and the discussion available in my BI Blog: www.pedrocgd.blogspot.com

For me DW should be denormalized for performance purposes... but you decide better after reading the draft v1.0 created by Marco Russo and Alberto Ferrari, that I thinks is easy to understand!
Helped?!
regards!
pedro
0
 
sburmanAuthor Commented:
Hi Pedro,

We we are not in DW . In a application we have a combination of both Normalized table or denormalized table. Application data models are either in 2N or 3N Form. I wanted to know the advantages to having a 2N over say 3N
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
UnifiedISCommented:
Nice article chapmandew, I quite enjoyed it.  

I agree that the technical developer should be listened to.  Making everything super-dynamic sounds appealing but think of just a state table with the State name and its abbreviation.  Wouldn't you rather type Select StateName From State Where Abbreviation = 'MI' than having to build a self-joining nightmare query to populate a dropdown list?

0
 
chapmandewCommented:
BrandonGalderisi sent that to me today...it was a very interesting read.  Glad you enjoyed it.  :)
0
 
dportasCommented:
sburman, the first part of your question seems to be about the so-called "OTLT" anti-pattern rather than about denormalization exactly. If I've understood you correctly then what you are proposing has big disadvantages because it's generally hard to impose the right candidate keys or referential constraints on such a model and perhaps impossible to use the right data types even (what types do you choose for the "value" columns?). See:
http://tonyandrews.blogspot.com/2004/10/otlt-and-eav-two-big-design-mistakes.html

The second part of your question is about denormalization, specifically adding non-key functional dependencies. There are two main reasons why you might do that:
- To allow some rule to be implemented which would otherwise be impossible or difficult to achieve.
- To force some physical change that would otherwise be impossible or difficult

Without specific examples it's impossible to say whether anything like that is justified. In general it's best to design to at least BCNF or 5NF unless and until you find a compelling reason to do otherwise.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now