Solved

Using Denomalized Table in a application

Posted on 2008-10-30
7
226 Views
Last Modified: 2009-01-11
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
Comment
Question by:sburman
7 Comments
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 125 total points
ID: 22844191
Listen to your technical developer...and read this article.

http://www.simple-talk.com/opinion/opinion-pieces/bad-carma/ 
0
 
LVL 22

Assisted Solution

by:PedroCGD
PedroCGD earned 125 total points
ID: 22844246
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
 

Author Comment

by:sburman
ID: 22845281
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 18

Assisted Solution

by:UnifiedIS
UnifiedIS earned 125 total points
ID: 22845384
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 22845402
BrandonGalderisi sent that to me today...it was a very interesting read.  Glad you enjoyed it.  :)
0
 
LVL 22

Accepted Solution

by:
dportas earned 125 total points
ID: 22888908
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now