Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 20th, 2004, 01:25 PM
Registered User
 
Join Date: Jan 2004
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Risk assessment of not normalizing a table

First let me thank all of you who will take the time to read this.

I need to keep track of a list of actions that will be executed when a certain event occurs (eg. timer event). There are twelve possible actions in my application, but it will be sufficient as far as my question is concerned if I just talk about two of those twelve possible actions. The first action is, I want to play a beep with a certain frequency and duration, and the second action is I want to play a wav file. The first action requires two parameters to the Beep command (frequency and duration) while the second action requires only one parameter to the PlayWavFile code (the wav filename). My initial design of the actions table looks like this (for clarity sake I have not shown foreign keys into other tables, I have shown only those fields that are pertinent to my question):

ActionId int 4 PrimaryKey Identity
ActionType int 2
Parameter1 varchar(128) Allow nulls
Parameter2 varchar(128) Allow nulls
Parameter3 varchar(128) Allow nulls

I have an enum which defines the twelve possible action types:
PlayBeep=1, PlayWavFile=2, PrintDocument=3, etc.
If I wanted to execute the two actions PlayBeep and PlayWavFile, my table would look like this:

ActionId, ActionType, Parameter1, Parameter2, Parameter3
101, 1, 1000, 500, null
102, 2, c:\chimes.wav, null, null

For the first row: An ActionType equal to 1 is the PlayBeep command, so it would pass 1000 as the frequency and 500 as the duration to the Beep api. For the second row: An ActionType equal to 2 is the PlayWavFile command so it would pass c:\chimes.wav to the PlayWavFile code.

[As an aside:
1) To implement this in vb code is trivial. Do a Select Case on the ActionType and then pass the parameters to the appropriate routine.
2) Only one of the twelve possible commands requires three parameters.]

According to, "SQL Server 2000 Database Design by Louis Davidson" on page 153 paragraph "Fieldnames with Numbers at the End", my understanding is that this design is very blatantly wrong and violates the First Normal Form.

My understanding is that this design is also in violation of the Third Normal Form, which is discussed on Page 157, 158 of the same book. "All attributes must be a fact about the key and nothing but the key". In my design the Parameters are facts about the ActionType not the ActionId.

My understanding is that in order to normalize this table it would require that I break this into twelve separate tables one for each possible action. In other words the PlayBeep table would have two fields specifically called Frequency and Duration while the PlayWavFile table would have a column specifically called WavFilename.

This would have the following negative impacts:
1) Gui front end to deal with twelve tables instead of just one.
2) The engine, the code that actually executes the actions, would now
   have to read twelve tables instead of just one.
3) If someday I need to add another action, I would have to build
   another table instead of just defining another actiontype.

Is there another way to redesign this table such that it can be normalized without all the negative impacts?

If the current design turns out to be the best way to design the table, what is the risk assessment of not normalizing?

I thank you in advance for your time,
Michael



 
Old January 20th, 2004, 02:06 PM
joefawcett's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

You don't need to break it that way. Have a parameters table, tblParameter like this:
ParameterId int 4(Primary Key)
ParameterActionFK int 4 (Foreign Key to tblAction)
ParameterValue Varchar(128)
ParameterPosition int 2

Thyen you need a join to establish parameters:
Code:
SELECT A.ActionType, P.ParameterValue
FROM tblAction A Inner Join tblParameter P
ON A.ActionId = P.ParameterActionFK
WHERE
A.ActionId = 101  /* Or whatever criteria you need */
ORDER BY A.ActionId, P.ParameterPosition
Then loop through recordset and pass parameters to method.
The disadvantage is it is slightly more troubleto do inserts and updates, the advantages are that new methods with more than three parameters are easily added and the tables are now normalised.




--

Joe
 
Old January 25th, 2004, 11:43 AM
Registered User
 
Join Date: Jan 2004
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you Joe. I don't know why I didn't see this myself. Again thank you.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Populate a List Box with Table Names & Table date hewstone999 Access VBA 1 February 27th, 2008 10:10 AM
Normalizing topshed Classic ASP Databases 0 April 30th, 2005 10:24 PM
Update parent table with the sum of child table gbrown SQL Language 2 November 9th, 2004 07:53 AM
(oracle 8i)Alter Table <table> coalesce partition combo Oracle 3 October 13th, 2004 09:35 AM
size of table (type table is table of number) MikoMax Oracle 1 November 19th, 2003 03:11 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.