thp_pkmi wrote:

> I have a table that always contains 1 record and have
> about 50 fields with various type. It's function is to
> store system parameters and is accessed very frequent
> from application and also from database triggers and
> stored procedures. Is it wise to utilize a singleton
> table like this, or is it better to implement a table
> with many records storing parameter name, type, and
> values ?

Both approaches have benefits and drawbacks:

1. single record

Since possible "parameters" are table column they are predefined and you can implement constraints easily (for example some parameter might be between 0 and 100, it's easy to write CHECK PAR1 BETWEEN 0 AND 100). With multiple rows, you would have to implement triggers on UPDATE and INSERT and check if field name is PAR1 and then the value. Problem with single record is that any change has to be DDL, so users might have to logout and connect back to see the new setting. So it's harder if you roll out database changes dynamically while everyone is connected and working.

I would choose this if there are multiple developers working on a project and/or parameters are not added frequently.

2. multiple records

It's easy to add new parameters and remove old ones, just with simple INSERT/DELETE. OTOH you could have problems if a record is missing, so you need to make sure each SELECT from the table should be ready to get no rows returned. Ex. if you have a parameter which can be 1 or 0, it is not enough to check if first row returns 1:

select value from partable where parname = 'PAR2';

but you also need to check if there was a row at all.

OTOH if you wrap this into some nice code or a stored procedure, you could have less records in that table, and only store values that override defaults. So if you have many databases with different clients, clients with default setup would have an empty table, and those who are different would have only records for settings that are different than default.

I would not use this approach myself, as in projects I work on we often dump/copy databases around and if you forget to copy parameters table the application could behave differently and confuse users or cause damage. So we switched to approach 1. with a single row.

In short: don't worry about speed, you could access this table thousands of times per second and still not notice. Worry about your ability to maintain it.

HTH

--
Milan Babuskov

==================================
The easiest way to import XML, CSV
and textual files into Firebird:
http://www.guacosoft.com/xmlwizard
==================================