mixing misc data in one DB table. Bad? OK? Good?

Report · May 07, 2006

I want to store a handful of different types of config data in my DB.

Maybe 4 types of content with 1-4 records for each.

It seems silly to make a half-dozen DB tables to just store a handful of
records each and then it'd be cleaner to just make one table:

ConfigurationVariables:
variableName | variableValue| variableID

Is that acceptable? Some major DB faux paux?

-Darrel

Report · May 07, 2006

No it is not a feux paux. Howevere if the content contain different types
then you may want to add the field to denote the type. This will make bring
the results to the screen easier.

--
Paul Whitham
Certified Dreamweaver MX2004 Professional
Adobe Community Expert - Dreamweaver

Valleybiz Internet Design
www.valleybiz.net

"Darrel" <notreal@nospam.com> wrote in message
news:e3m2jf$eif$1@forums.macromedia.com...
>I want to store a handful of different types of config data in my DB.
>
> Maybe 4 types of content with 1-4 records for each.
>
> It seems silly to make a half-dozen DB tables to just store a handful of
> records each and then it'd be cleaner to just make one table:
>
> ConfigurationVariables:
> variableName | variableValue| variableID
>
> Is that acceptable? Some major DB faux paux?
>
> -Darrel
>

Report · May 08, 2006

You're treading dangerous ground. If you go too far down this road, you'll
be implementing an EAV architecture (entity-attribute-value), which is not
healthy when you're using an RDBMS.

If you're only going to have a handful of records, and you're not expecting
the values to change much, what's your reason for putting them in the
database? Do you need to use the variable table in joins for your queries?
Things like configuration variables are better stored in an XML file, which
has replaced the .ini text file on the web so people can think they're cool.

If there's more to the story, then please let me know.

"Darrel" <notreal@nospam.com> wrote in message
news:e3m2jf$eif$1@forums.macromedia.com...
>I want to store a handful of different types of config data in my DB.
>
> Maybe 4 types of content with 1-4 records for each.
>
> It seems silly to make a half-dozen DB tables to just store a handful of
> records each and then it'd be cleaner to just make one table:
>
> ConfigurationVariables:
> variableName | variableValue| variableID
>
> Is that acceptable? Some major DB faux paux?
>
> -Darrel
>

Report · May 08, 2006

> If you're only going to have a handful of records, and you're not
> expecting the values to change much, what's your reason for putting them
> in the database?

Easier to edit via an admin interface.

I could use XML too, I suppose. But same concern there, I guess.

> Do you need to use the variable table in joins for your queries?

Nope.

> Things like configuration variables are better stored in an XML file,
> which has replaced the .ini text file on the web so people can think
> they're cool.

Oh, but, really, what's the difference? XML is really a database too. Just
stored differently.

> If there's more to the story, then please let me know.

No, not really. This table just stores a handful of variables for maybe 3
different tasks that have to do with setting configuration details about the
CMS...a list of default image resizing dimensions, a list of default
template files, maybe the footer text for the entire site. Basically the
'misc data' table ;o)

-Darrel

Report · May 08, 2006

> No, not really. This table just stores a handful of variables for maybe 3
> different tasks that have to do with setting configuration details about
> the CMS...a list of default image resizing dimensions, a list of default
> template files, maybe the footer text for the entire site. Basically the
> 'misc data' table ;o)

It sounds like a config table will be fine. I would avoid the xml file
unless the data is not critical, or you will have to put it in a non-web
folder to avoid prying eyes. I think the db is faster in general than trying
to access the file system.

--
Tom Muck
co-author Dreamweaver MX 2004: The Complete Reference
http://www.tom-muck.com/

Cartweaver Development Team
http://www.cartweaver.com

Extending Knowledge Daily
http://www.communitymx.com/

Report · May 08, 2006

"darrel" <notreal@nowhere.com> wrote in message
news:e3njkq$jn9$1@forums.macromedia.com...
>> If you're only going to have a handful of records, and you're not
>> expecting the values to change much, what's your reason for putting them
>> in the database?
>
> Easier to edit via an admin interface.

Yep. That's true.

>
>> Things like configuration variables are better stored in an XML file,
>> which has replaced the .ini text file on the web so people can think
>> they're cool.
>
> Oh, but, really, what's the difference? XML is really a database too. Just
> stored differently.
>

Umm - that's so far off I don't even want to start. An XML file is a text
file. If your database is performing like a text file, you've got issues.

>> If there's more to the story, then please let me know.
>
> No, not really. This table just stores a handful of variables for maybe 3
> different tasks that have to do with setting configuration details about
> the CMS...a list of default image resizing dimensions, a list of default
> template files, maybe the footer text for the entire site. Basically the
> 'misc data' table ;o)

One table to hold some misc junk like that is ok. Just don't go too far,
ok? The things you've listed don't seem to be a real problem. I'd just use
a name/value table with the value column being VARCHAR(8000) and let your
app do all the type checking. I still think a config file is best, but it's
not the easiest. You'd use your global.asa to cache the values from the
config file into the Application object (or whatever equivalent you're
using). That would mitigate performance issues that would come from
accessing a text file. For security, you'd store the file outside the site
root and use user impersonation to access the file when necessary - not
necessarily easy to set up.

Anyway, your proposed table doesn't sound too bad, but I've seen some real
messes that rose out of that kind of thinking.

Report · May 08, 2006

> Umm - that's so far off I don't even want to start.

Really?

I've always considered most anything that stores data in some sort of
meaningful 'chunks' to be considered a database.

A comma delimted text file, an XML file, a spreadsheet, a relational
database, etc.

Granted, they are all very different and some are definitely better at
other's at certain tasks. ;o)

> One table to hold some misc junk like that is ok. Just don't go too far,
> ok?

Cool. That was my hunch, but didn't want to do anything that was seen as
really bad practice.

> I still think a config file is best, but it's not the easiest.

We're actually using a config file as well. It's a .net app, so, by default,
it uses an XML config file already. We'll store a few variables in there,
but those will mainly be 'expert' type things that I don't think most folks
will want/need to change. If they do, they'd be skilled enough to know how
to open the file directly and make the changes manually.

> Anyway, your proposed table doesn't sound too bad, but I've seen some real
> messes that rose out of that kind of thinking.

Good advice, as always, Lionstone. Thanks!

-Darrel

Report · May 08, 2006

"darrel" <notreal@nowhere.com> wrote in message
news:e3nsd6$2fj$1@forums.macromedia.com...
>> Umm - that's so far off I don't even want to start.
>
> Really?
>
> I've always considered most anything that stores data in some sort of
> meaningful 'chunks' to be considered a database.
>
> A comma delimted text file, an XML file, a spreadsheet, a relational
> database, etc.
>
> Granted, they are all very different and some are definitely better at
> other's at certain tasks. ;o)

Maybe the dictionary definition (though I haven't looked it up). But most
people use "database" as shorthand for a relational database management
system. Saying "XML is a database" would definitely convey the wrong
message since most people (including me) hear that as "XML is an RDBMS."

Anyway, I'm not trying to play semantics. I think you knew what I meant at
any rate.