Config Tables Design

[Gareth]

Hi NG,

I'm revisiting one of my applications and was wondering whether I
could/should normalize table that stores configuration data. I have over
20 different fields of configuration data that I store in one table,
with one row for the System Defaults (or data unique to the system) and
then one row for each user where I store their customized settings.

For example, currently:

tblSystemConfig
-----------------
UserID (foreign key from tblUsers - or 0 if SystemDefault)
DBName (Only used for System)
DBVersionNo (Only used for System)
AppFormWidth (Numeric - SystemDefault)
AppFormHeight (Numeric)
AppIsMaximized (Boolean)
BackGroundColor (Numeric)
DisplayStart (Date)
DisplayEnd (Date)
etc. ~ 25 fields: some dates, boolean, strings, numbers (also some BLOBs
for bitmaps and things but I can keep them separate anyway)

In order to normalize I suppose I should do something using three
tables, one for the system settings, one for users and one for setting
types e.g.

tblSettingTypes
---------------
SettingID
SettingName (e.g. DisplayStart, AppFormWidth)

tblSystemSettings
---------------
SettingID (foreign key from tblSettingTypes)
SettingValue

tblUserSettings
---------------
UserID (foreign key from tblUsers)
SettingID (foreign key from tblSettingTypes)
SettingValue

This would definitely be more normalized but is slightly longerwinded
when I load or update settings - I can no longer just say "SELECT * FROM
tblSettings WHERE UserID = 0" for example. I have to stipulate exactly
which fields I want so that I get them in the right order. (I think
using ORDER BY SettingName or SettingID is workable but... not exactly
nice.)


Note that the SettingValue may be a date, boolean, string or number.
However, I thought I could get away with having the SettingValue field
as Text (as in Duane Hookum's survey example DB) and then coercing to
the correct datatype in my front end (VB app, connecting thru ADO).

Is this recommended or should I make separate tables
tblUserSettingsDates, tblUserSettingsBoolean, tblUserSettingsStrings
etc. with the correct datatype for the SettingValue field?

Any thoughts on recommended working practises gratefully received.

Thanks
Gareth

 

[Vincent Johns]

Hi NG,

I'm revisiting one of my applications and was wondering whether I
could/should normalize table that stores configuration data. I have over
20 different fields of configuration data that I store in one table,
with one row for the System Defaults (or data unique to the system) and
then one row for each user where I store their customized settings.

Based on what I infer from your field names, it doesn't look as if you
have a normalization problem in either design. Normalization involves
eliminating redundant information in the form of fields that are stored
in a Table but which you could have calculated. None of your fields
appear to be easy to calculate based on any of the other fields. (But
bear in mind that I'm guessing at the contents based on your field names.)

For example, currently:

tblSystemConfig
-----------------
UserID (foreign key from tblUsers - or 0 if SystemDefault)
DBName (Only used for System)
DBVersionNo (Only used for System)
AppFormWidth (Numeric - SystemDefault)
AppFormHeight (Numeric)
AppIsMaximized (Boolean)
BackGroundColor (Numeric)
DisplayStart (Date)
DisplayEnd (Date)
etc. ~ 25 fields: some dates, boolean, strings, numbers (also some BLOBs
for bitmaps and things but I can keep them separate anyway)

In order to normalize I suppose I should do something using three
tables, one for the system settings, one for users and one for setting
types e.g.

tblSettingTypes
---------------
SettingID
SettingName (e.g. DisplayStart, AppFormWidth)

tblSystemSettings
---------------
SettingID (foreign key from tblSettingTypes)
SettingValue

tblUserSettings
---------------
UserID (foreign key from tblUsers)
SettingID (foreign key from tblSettingTypes)
SettingValue

This would definitely be more normalized

I doubt it. And, although this proposed design would probably be
usable, you would lose the use of a safeguard that Access normally
supplies. Since [tblSystemSettings].[SettingValue] would contain data
of any of several types, Access wouldn't be able to warn you that you
were (e.g.) trying to stuff a date/time value into a currency field.
With your old design, that would be more obvious.

Warning: No matter how much error checking your database does, it is not
in general possible to identify WRONG information, only malformed
information. If your computer could judge the accuracy of what's
entered, you'd have no business asking for it to be entered in the first
place -- you should just compute it.

but is slightly longerwinded
when I load or update settings - I can no longer just say "SELECT * FROM
tblSettings WHERE UserID = 0" for example. I have to stipulate exactly
which fields I want so that I get them in the right order. (I think
using ORDER BY SettingName or SettingID is workable but... not exactly
nice.)

You'd do that by specifying a criterion for each desired field. That
could work, but I don't see that it gives you any benefit.

Note that the SettingValue may be a date, boolean, string or number.
However, I thought I could get away with having the SettingValue field
as Text (as in Duane Hookum's survey example DB) and then coercing to
the correct datatype in my front end (VB app, connecting thru ADO).

Is this recommended or should I make separate tables
tblUserSettingsDates, tblUserSettingsBoolean, tblUserSettingsStrings
etc. with the correct datatype for the SettingValue field?

Any thoughts on recommended working practises gratefully received.

Thanks
Gareth

Is this a class exercise, or is there some benefit that you expect to
derive from restructuring your Table? Remember that Access will allow
you to include a couple of hundred fields in each record (though the
largest records I've ever used have contained maybe 30 or 40 fields).
If you can identify a collection of fields which are frequently all Null
in the same record, you might (but not necessarily would) consider
moving those fields to a separate Table, with a 1:1 link to the first
Table, and in which you'd omit records with all-Null fields. But that's
a pretty specialized situation, and I can see no reason to want to do
that if you have only a few dozen records in your entire Table.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Gareth]

Hi Vincent,

Thanks for your reply. Your inference with respect to the field names
and the nature of the data is correct: it's not really a normalization
issue, it's more for tidiness and wishing to conform with accepted
practises. It just feels a little odd having a table with 25 fields (and
I may push it up to 40) where I may only have 10 or so records (No of
users + 1) - I understand there's a limit of 255 fields in Access and
I'm confident I won't hit triple figures no matter how much the system
evolves.

I'm less concerned about the integrity of the data since the front end
applies all necessary constraints before updating the database and,
further, checks it all on load.

This isn't a class exercise -- rather this a working application. I was
just housekeeping the front end and thought I might springclean the DB
too. Since the first scenario is what I already have in place then I'm
quite happy to leave it like that.

Thanks again,
Gareth

Hi NG,

I'm revisiting one of my applications and was wondering whether I
could/should normalize table that stores configuration data. I have
over 20 different fields of configuration data that I store in one
table, with one row for the System Defaults (or data unique to the
system) and then one row for each user where I store their customized
settings.

Based on what I infer from your field names, it doesn't look as if you
have a normalization problem in either design. Normalization involves
eliminating redundant information in the form of fields that are stored
in a Table but which you could have calculated. None of your fields
appear to be easy to calculate based on any of the other fields. (But
bear in mind that I'm guessing at the contents based on your field names.)

For example, currently:

tblSystemConfig
-----------------
UserID (foreign key from tblUsers - or 0 if SystemDefault)
DBName (Only used for System)
DBVersionNo (Only used for System)
AppFormWidth (Numeric - SystemDefault)
AppFormHeight (Numeric)
AppIsMaximized (Boolean)
BackGroundColor (Numeric)
DisplayStart (Date)
DisplayEnd (Date)
etc. ~ 25 fields: some dates, boolean, strings, numbers (also some
BLOBs for bitmaps and things but I can keep them separate anyway)

In order to normalize I suppose I should do something using three
tables, one for the system settings, one for users and one for setting
types e.g.

tblSettingTypes
---------------
SettingID
SettingName (e.g. DisplayStart, AppFormWidth)

tblSystemSettings
---------------
SettingID (foreign key from tblSettingTypes)
SettingValue

tblUserSettings
---------------
UserID (foreign key from tblUsers)
SettingID (foreign key from tblSettingTypes)
SettingValue

This would definitely be more normalized

I doubt it. And, although this proposed design would probably be
usable, you would lose the use of a safeguard that Access normally
supplies. Since [tblSystemSettings].[SettingValue] would contain data
of any of several types, Access wouldn't be able to warn you that you
were (e.g.) trying to stuff a date/time value into a currency field.
With your old design, that would be more obvious.

Warning: No matter how much error checking your database does, it is not
in general possible to identify WRONG information, only malformed
information. If your computer could judge the accuracy of what's
entered, you'd have no business asking for it to be entered in the first
place -- you should just compute it.

but is slightly longerwinded when I load or update settings - I can no
longer just say "SELECT * FROM tblSettings WHERE UserID = 0" for
example. I have to stipulate exactly which fields I want so that I get
them in the right order. (I think using ORDER BY SettingName or
SettingID is workable but... not exactly nice.)

You'd do that by specifying a criterion for each desired field. That
could work, but I don't see that it gives you any benefit.

Note that the SettingValue may be a date, boolean, string or number.
However, I thought I could get away with having the SettingValue field
as Text (as in Duane Hookum's survey example DB) and then coercing to
the correct datatype in my front end (VB app, connecting thru ADO).

Is this recommended or should I make separate tables
tblUserSettingsDates, tblUserSettingsBoolean, tblUserSettingsStrings
etc. with the correct datatype for the SettingValue field?

Any thoughts on recommended working practises gratefully received.

Thanks
Gareth

Is this a class exercise, or is there some benefit that you expect to
derive from restructuring your Table? Remember that Access will allow
you to include a couple of hundred fields in each record (though the
largest records I've ever used have contained maybe 30 or 40 fields). If
you can identify a collection of fields which are frequently all Null in
the same record, you might (but not necessarily would) consider moving
those fields to a separate Table, with a 1:1 link to the first Table,
and in which you'd omit records with all-Null fields. But that's a
pretty specialized situation, and I can see no reason to want to do that
if you have only a few dozen records in your entire Table.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

Hi Vincent,

Thanks for your reply. Your inference with respect to the field names
and the nature of the data is correct: it's not really a normalization
issue, it's more for tidiness and wishing to conform with accepted
practises. It just feels a little odd having a table with 25 fields (and
I may push it up to 40) where I may only have 10 or so records (No of
users + 1) - I understand there's a limit of 255 fields in Access and
I'm confident I won't hit triple figures no matter how much the system
evolves.

Something else that I think you want to avoid, besides unnormalized
Tables, is having several fields that contain essentially the same types
of information, such as [UserName1], [UserName2], etc. (This is akin to
what is sometimes called "repeating groups".) Those would be prime
candidates for moving into another Table with just one field to contain
all of those similar data. But I didn't see anything like that here.
And I also wouldn't worry much about how many records there are. At
times, I've used a Table with dozens of fields and only one record, as a
means of tracking a collection of variables (without having to use a
Module to do that, and without storing the values in a collection of
controls on a Form).

I'm less concerned about the integrity of the data since the front end
applies all necessary constraints before updating the database and,
further, checks it all on load.

This isn't a class exercise -- rather this a working application. I was
just housekeeping the front end and thought I might springclean the DB
too. Since the first scenario is what I already have in place then I'm
quite happy to leave it like that.

Thanks again,
Gareth

It's possible that others on this NG would give you different (perhaps
contradictory) advice from what I've suggested. If so, I hope they post
it. But from what I can determine, I don't anticipate that you will get
into any trouble based on the table design you described. I don't see
much wrong with it. Good luck!

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Jeff Boyce]

Gareth

Vincent makes excellent points.

I've used both approaches. For example, I have a "report order" table
design that informs the controls on the form whether they are on/off, what
their label text/caption should read, what default value, if any, they hold,
etc. In its early incarnations, it was as you've described your "wide"
table.

However, as users kept adding more required "selection criteria" to the
reports, I began finding the "open wider" design to start presenting small
issues. For me, remembering which of the 20+ fields returned by the query
were associated with which of the multiple controls on the form got
confusing.

So I've switched the listing of "report order" fields, their enabled status,
default value, label captions, etc. over to a design much like what you've
posted about. Now, as my users get more creative (i.e., insistent), I can
add a control to the form and add a few records to the table, rather than
re-defining the table structure, the query, the mapping from the
cboSelectReport combo box to the controls, etc.

As I said at the top, though, I'm with Vincent -- I, too, don't see a
normalization issue.

 

[Gareth]

Yup - again, well inferred. UserNames etc. are stored in a separate table:

tblUsers
--------------
PKID
UserName
LastName
OtherNames
Password (encrypted)
IsActive

With the PKID as the UserID field in tblSettings -- so I'm not worried
about normalization there.

Thanks again for your advice, makes me feel more confident in my schema.

Gareth

Hi Vincent,

Thanks for your reply. Your inference with respect to the field names
and the nature of the data is correct: it's not really a normalization
issue, it's more for tidiness and wishing to conform with accepted
practises. It just feels a little odd having a table with 25 fields
(and I may push it up to 40) where I may only have 10 or so records
(No of users + 1) - I understand there's a limit of 255 fields in
Access and I'm confident I won't hit triple figures no matter how much
the system evolves.

Something else that I think you want to avoid, besides unnormalized
Tables, is having several fields that contain essentially the same types
of information, such as [UserName1], [UserName2], etc. (This is akin to
what is sometimes called "repeating groups".) Those would be prime
candidates for moving into another Table with just one field to contain
all of those similar data. But I didn't see anything like that here.
And I also wouldn't worry much about how many records there are. At
times, I've used a Table with dozens of fields and only one record, as a
means of tracking a collection of variables (without having to use a
Module to do that, and without storing the values in a collection of
controls on a Form).

I'm less concerned about the integrity of the data since the front end
applies all necessary constraints before updating the database and,
further, checks it all on load.

This isn't a class exercise -- rather this a working application. I
was just housekeeping the front end and thought I might springclean
the DB too. Since the first scenario is what I already have in place
then I'm quite happy to leave it like that.

Thanks again,
Gareth

It's possible that others on this NG would give you different (perhaps
contradictory) advice from what I've suggested. If so, I hope they post
it. But from what I can determine, I don't anticipate that you will get
into any trouble based on the table design you described. I don't see
much wrong with it. Good luck!

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Gareth]

Hi Jeff,

Thanks for your post. You've hit one of my other (unvoiced) concerns:
future expandability. If I need to add a new field in the future - let's
say I decide to remember the report a user last used and default to that
- it would be a lot easier if I use a "thin" table (to borrow your
terminology). I can then just deploy a new executable which will
tolerate missing records and update the DB as necessary on application
close. With the "wide" table I would need to actually amend table design
to implement that -- which, while achievable programmatically, is
nowhere near as neat a solution and would mean I would always have to
check for the new field's existance in my exe (since I allow the user to
roll back to previous snapshots of the DB should they feel the need).

Since neither you nor Vincent recoiled from the wide design I think I'll
stick with that for now -- just to save recoding. I'm looking to port to
..NET from VB 6 eventually and I'll look to the thin design for that
implementation.

Thanks again for your help,
Gareth