Storing settings

[EricTheRed]

(Seriously, I have no idea what I'd do without this forum. Thanks to all who continue to reply to everyone's queries!)

I suppose this is a general design question. I have a number of settings and parameters - editable by the end-users - throughout my database. Things like SMTP information, maintenance settings, et cetera, though everything is either numerical or textual. Here's the question: how should I store all of this stuff?

A table with only one record that's 20 fields wide sounds like a bad idea, so I'm kind of at a dead end. Ideas, anyone?

 

[llkhoutx]

Twenty fields for a table is not bad at all. Be sure that they're each independent of one another, i.e. normalized.

 

[EricTheRed]

Yeah, but 20 is just the beginning. I imagine as the database grows there will be need to add more settings. It is entirely possible that I will hit the width limit eventually, which is 255 is it not?

Though maybe its not really a problem and I'm making a mountain out of a molehill. Hmmm.

 

[llkhoutx]

I think you are. Just try it, if it doesn't work try someting else.

I have a report with 79 subports, each with 3 header text boxes on the report, which I back fill at runtime depending on whether or not they have data to report. I backfill the each subreport textbox (text) label with with a null or a label depending if non-null data exists for the associated textbox to facilitate collapsing the subform to zero height, as CanGrow/CanShrink won't shrink a label associated with a null text box (or at least I can't make it shrink). I created all the rsubreports programmatically; manually it woulb be a brute. The object of this is that I save a lot (hundreds) of user defined properties.

 

[The_Doc_Man]

Eric, there is nothing to say that you couldn't define more than one table if you hit the magic 255 number. But if you have a few items that include text, you'll probably hit the other number as well. The 2048 max record length is the one I mean.

 

[KKilfoil]

I don't know if this is good style, but I have a database with a great number of text and numeric 'constants' used in other calculations, forms, reports, etc.

I created [tblConstants] with 5 fields:
[ConstantID] - autonumber PK
[ConstName] - short name of the constant, uniquely indexed
[Description] - text field - more detailed description of the constant
[txtValue] - text field - text value of the constant, if any
[numValue] - numeric field - numeric value of the constant, if any.

Then I just populated the appropriate Value field, numeric or text, depending on the nature of the constant, and use dlookup to get the constant values as I need them elsewhere in my db.

If you want the end users to be able to change theses values, a form would be easy to set up.

You can add new constants easily just by adding records to the table.