Storing Settings

[Gman]

Hi NG,

I have an Access DB with a VB front end connecting through ADO. I have a
table containing settings with around 30 fields. By way of example

tblSETTINGS
UserID
FormAWidth
FormAHeight
FormAColor
FormBWidth
FormBHeight
FormBColor
PathA
PathB
etc.

This works great - as discussed in a post a while back - since I can get
all the settings for a user in one recordset row in one fell swoop.

The (big) limitation of this though is that if I want to add a new
setting for a new version of the application, let's say FormCWidth, I
need to modify the table design. Since I have multiple instances of this
database it means I need to modify all the instances. This is a real
pain as you can imagine - I would rather just deploy a new executable.

Therefore I think a better solution would be to have four tables

tblSETTINGSNUMERIC
tblSETTINGSTEXT
tblSETTINGSDATES
tblSETTINGSBOOLEANS

e.g.

tblSETTINGSNUMERIC
UserID (numeric)
SettingName (text)
SettingValue (numeric, date, boolean, text depending on table)

This way I can add (and indeed remove) settings as I require without
having to modify the schema.

So far so good.

This strikes me as denormalized since I will have multiple occurrences
of the SettingName in each table. Therefore it would seem to be better
design to have the settings tables designed thus:

UserID (numeric) } PK
SettingTypeID (numeric) } PK
SettingValue (numeric, date, boolean, text depending on table)

with a fifth table:

tblSettingTypes
SettingTypeID }PK
SettingTypeName

But this makes constructing the INSERT SQL a little trickier... but doable.

INSERT INTO SettingsStrings
(UserID,SettingTypeID, SettingValue)

SELECT 1 as myUserID , PKID , 'I am a string too' as myValue
FROM SettingTypes
WHERE SettingName = 'SettingTwo'

But it means I have to insert each Setting record individually. Not
great... but I can live with it.

My questions are as follows:

(1) This must be a common requirement (right?) is there a common practise?

(2) Should I really be bothered about normalization for a table like
this and therefore I could just use 4 tables?

(3) Has anyone got any better ideas?

(4) Would anyone recommend the approach of having the application check
the structure of the Settings table and adding the new field if missing?
Sounds nasty to me.

Many thanks in advance,

Gman

 

[Gman]

Gman wrote:

As an afterthought, would I be better off storing data using a recordset
rather than using multiple inserts wrapped in a transaction?

Thanks

 

[Tim Ferguson]

tblSETTINGS
UserID
FormAWidth
FormAHeight
FormAColor
FormBWidth
FormBHeight
FormBColor
PathA
PathB
etc.

Whoa: big warning glags going off here. What happened to the Forms table
and the Paths table and the etc. Thirty fields is a pretty wide table --
yes it needs normalising.

The (big) limitation of this though is that if I want to add a new
setting for a new version of the application, let's say FormCWidth, I
need to modify the table design.

See above...

Therefore I think a better solution would be to have four tables

At least four, to be honest...

tblSETTINGSNUMERIC
tblSETTINGSTEXT
tblSETTINGSDATES
tblSETTINGSBOOLEANS

.... but not these ones!

e.g.

tblSETTINGSNUMERIC
UserID (numeric)
SettingName (text)
SettingValue (numeric, date, boolean, text depending on table)

This strikes me as denormalized since I will have multiple occurrences
of the SettingName in each table.

Huh? What definition of normal are you referring to? Normalisation refers
to the dependency of columns on the primary key: presumably the PK of
this is (UserID, SettingName) and the only non-key field is SettingValue
and that is clearly dependent only on the whole key. Ergo in 3NF.

FWIW, if I really wanted to go down this route, I'd have a single table
with all the values (probably a text field) and another method of
controlling the domains.

(1) This must be a common requirement (right?) is there a common
practise?

Classic example of this approach is AtYourSurvey -- suggest you have a
look at that. But I still doubt it's the best solution for what looks
like your problem.

(2) Should I really be bothered about normalization for a table like
this and therefore I could just use 4 tables?

You should always be "bothered about normalisation"; but you might find
out what it is first.

(3) Has anyone got any better ideas?

Start by analysing your entities properly first.

Best wishes


Tim F

 

[Albert D.Kallal]

why not just one talbe?

UserID Setting Value
Albert AormAWidth 4200

It seems to me that a table with 3 columns would do the trick.....

So, normalizing your data as above would eliminate your 30 fields..and now
you have just 3 fields.

I suppose you could normalize another order..and have two tables
tblUsers
tblUserSettings

I would use two tables if you already have a table for user name, user
initials, and some things. If you have that table now, then sure, I would go
with two tables as to allow easy change, or deletion of a particular user.

 

[Gman]

Hi Tim,

Thanks for your quick response.

<Thirty fields is a pretty wide table -- yes it needs normalising. >
<vbg> - Yup, I was concerned about this originally too. I actually
broached the subject on this NG but the general consensus was that it
wasn't because my data (at the time) wasn't that repetitious... it's
just evolved a tad....

My reasoning that the four table model wasn't normalised was that I was
storing the same text in SettingName again and again for each user,
which is -- normally -- considered denormalisation. As you suggest
though, if I make (UserID, SettingName) the PK then it will of course be
normalised. (That didn't occur to me for some reason... wood, trees etc.
so thank you for pointing it out.)

Thanks for the reference to AtYourSurvey. I took a look at that a while
ago. I shied away from using that approach because I wasn't keen on
storing my values as text (since Access doesn't have a Variant data type
for fields). Maybe I should revisit it.

I take your point wrt to having tables such as Forms, Paths etc. In
truth I'm currently only storing the values for one form - I just think
I should expand that. So a more accurate example of my data would be

FormMainHeight (Long)
FormMainWidth (Long)
FormMainIsMaximized (Boolean)

GridStartTime (Date)
GridEndTime (Date)
GridRefreshFrequency (Numeric)
GridRowHeight (Numeric)
GridColWidth (Numeric)
GridWeekDayLayout (String)
GridZoom (Numeric)
GridBackgroundColor (Numeric - long)

RecordColorWarningOnly (Numeric - long)
RecordColorMandatory (Numeric - long)
DefaultRecordDuration (Date)

ShowHorizontalGridLines (Boolean)
ShowVerticalGridLines (Boolean)

LastExportPath (String)
LastImportPath (String)

Thus, there's (currently) little repetition. But I want all of this to
be future proofed as the application evolves (generally in reponse to
users' requests). For example, the two ShowGridlines booleans were
recently added and I had to modify the database. If I had tables such as
Forms, Colors, Grids (say) I would still have had to modify a table
somewhere database because I wouldn't have anticipated these two
additional settings types. (And who knows what's next around the
corner... DefaultExportType (PDF/Excel/Text) or LastSelectedClient
maybe....)

In summary, I'm really keen to keep this as generic as possible. At the
nervy back of my mind I would still prefer to have separate tables for
different datatypes - but facing up to your comments, Duane Hookum's
AtYourSurvey and Mr Kallal's post that just popped up... I suppose I
should stop being such a whiner and accept that storing miscellaneous
datatypes as strings isn't such a crime after all!

With respect to the AtYourSurvey tack, you wrote <I still doubt it's the
best solution for what looks like your problem.> Would you propose
something like:

FormStates
Colors
Paths
DateTimes
Boolean

Suggestions welcome.
Thanks again.
G

 

[Gman]

Hi Albert,

Thanks for your response. Ok..... I think I've been dissuaded from
multiple tables then. And your approach is certainly easier to implement
too!

You're quite correct in supposing I have two tables -- one with for
Users. I just felt a bit strange storing all these datatypes in there.

FYI would you have a preference as to how boolean values should be
stored? I've tried both -1/0 and True/False and they both seem to work
fine when coerced with CBool. (I couldn't open AtYourSurvey code in the
VBE to see how it's handled there 'cause it's Access97.)

Many thanks,
Gman

 

[Albert D.Kallal]

FYI would you have a preference as to how boolean values should be
stored?

Well, since our values are going to be a string, then in the debug window.

debug.print cstr(true)

The above produces a string of "true".

So, when you convert a Boolean to a string, you get "true", or "false".
While both cbool(-1), and cbool("true") both produce

True

Converting cbool to a string produces "true" and not -1...so, it stands to
reason that we store the text "true", or "false".

 

[Gman]

Um.... Yes. When you look at it from the perspective of converting a
boolean to a string - rather than retrieving a string and concerting to
boolean as I did - there really is no debate. It was therefore rather a
stupid question on my behalf.

So I thank you doubly for your patient response.
G

 

[Albert D.Kallal]

Um.... Yes. When you look at it from the perspective of converting a
boolean to a string - rather than retrieving a string and concerting to
boolean as I did - there really is no debate. It was therefore rather a
stupid question on my behalf.

No...since you could store the string -1, or 0 (for true and false).

So...no..your question was good!!

 

[Gman]

You're too kind!

No...since you could store the string -1, or 0 (for true and false).

So...no..your question was good!!