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

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:
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

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