Skip to main content

Preserving uniqueness without changing table structure

This one took me a year to realise.
Yes a bit thick!
It is so simple, one wonders why it was not visible before.

I had to design a database table for charges against a service.

The charge depends on various parameters.
The usual way of representing would be to put the parameter values and the results in single unique record

Eg:
Z= f(x,y,z)
Z= 40.9 when x=1 y=2 z= 3

Record
Z x y z
40.9 1 2 3

Blow up the same methodology for cases like 3<=x<=9.5
For this, you would need columns such as
GEx GTx EQx LEx LTx
Populate with values and you can define a condition

Repeat for each variable.

You will end up having a table with fairly large number of columns.

I did not want this.

Also I was wondering about cases where say Y is a string and not a number.
This was posed as a question to ether space via this blog in an post made in Apr09

Z = 80.345 when x=1,z=3 and Y contains string "Singh"
Say it defines a taxi rate to the airport when your name is same as the driver's.

The solution I found is to represent these conditions as multiple records in a different table which a link to the unique rate record held in the 'rate' table.

I am sure this has been done before and used commonly!
Anyway, no one was there to tell me.

Comments

Post a Comment

Popular posts from this blog

Siemens Washing Machine : Kill the Buzzer

The washing machine we have has the habit of sending out an irritating beep after it finishes the wash cycle. Ok enough; but these intermittent beeps go on and on and on till it is sure everyone came back home after your attending your funeral. One awful engineer who programmed the chip. Anyway, been looking around the net if there is a work around. Found this written by some Russian. I didn’t have patience to correct the grammar entirely. If the below does  not work, there is always the option of an Axe and ‘hey Siemens,..Heeeere is Johnyyy!) -- text You can change the volume of the buzzer according to your requirement. The operation procedure: 1. Switch on the machine,turn the program selector to Off . 2. Turn the program selector to cold Easy-care , press the additional function button Intensive stains and dont let go. You can hear the volume of the buzzer from minimum to maximum to off cycled (I didnt hear this!). If you decide the volume that yo...
3 comments
Read more

SQL Server - Get columns and data types from all tables

Useful script USE SmartHire GO  SELECT TABLE_NAME, COLUMN_NAME, COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID') AS COLUMN_ID, DATA_TYPE  FROM SmartHire.INFORMATION_SCHEMA.COLUMNS  GO   Output TABLE_NAME COLUMN_NAME COLUMN_ID DATA_TYPE TblBankDetails BankID 1 int TblBankDetails BankName 2 varchar TblContactPreferences ContactPreferenceID 1 int
Post a Comment
Read more

Getting ENUM values by Reflection

Recently I had this problem, where I had  to get the Items and Values of an Enum within a class. The class name and enum name (as strings) are known and the instance itself has to be created at runtime. i.e; via Reflection Namespace N1abcd .... Public Class TestClass     Public Enum TestEnum         val1 = 20         val2 = 30     End Enum End Class The syntax in VB.net is as below '   System.Type.GetType("Namespace Name" + "." + "Class Name" + "+" + "Enum Name") Dim fieldInfos() As System.Reflection.FieldInfo = System.Type.GetType(" N1abcd .TestClass+TestEnum").GetFields             For Each f As System.Reflection.FieldInfo In fieldInfos                 If f.IsLiteral Then            ...
Post a Comment
Read more