Single row tables

[FearsomeFastFemale]

Hi,
I can been presented with a part completed application. There are several parameters that change rarely.
The current design is a single row table that has a column for each parameter.

We need to add some more which relies on a 3 week turnaround from the DBA's

I would probably has a multiple row table so we add more parameters rows without requiring a table change. This would result in a table with about 20 rows. Of 2 columns (name and value) oppose a table of 1 row and 20 columns)

Is the single row table something that is more efficient in DB2 V7 on z/OS. Or is this a choice ?

Thank you of any information you can provide
FFF

 

[MarcLodge]

FFF,
I would imagine that it is table design based on non changing reference data and efficiency. It the row contains multiple items of reference data that rarely, if ever change, then obtaining all 20 parameters in one shot is obviously a far better bet than twenty table lookups.

If the number of parameters held on the table is liable to change during the life of the table, then the suggestion you describe is a much better and more flexible option.

I can only imagine that when the table was designed it was thought that the number of parameters (rather than the value) would never change, hence the design structure chosen.

Marc

 

[BrianTyler]

The big problem with the new solution is that, if the query needs to reference several of the parameters, a number of 'alias' table will be required. This may be difficult for end-users (or inexperienced programmers) to appreciate.

The one-row solution is fine, but you will need a way around the DBA. I suggest that you arrange for him to provide you with a script that you can use to add a new column to the table.

I think that the purest solution is to have a seperate table for each parameter, though I am open to persuasion on this.

Brian

 

[gregsimpson]

FFF,

I agree with Mark and I'm happy with your solution. I personally don't think you've got a performance problem in going from one table, one row with a number of columns to your new design of 1 table, 2 columns and a number of rows.

When DB2 reads a row of the data, it will more than likely pick up a number (if not all) of the rows on your new design if they are sitting on the same data page. Also if it's a table that gets used frequently the chances are the data will be held in the bufferpools, negating the need for futher physical access.

As a bonus you won't have to ask your DBA for an extra column everytime you need more reference data.

Cheers
Greg