(I did a quick Google groups search of this
group and didn't turn up anything; I suspect
I don't quite have the right vocabulary for
this question. Suggestion are welcome.)


I have a simple problem: I want to store
configuration information for all the businesses
using my software. There is also a set of
global defaults, so getting a configuration value
for a business involves checking to see if that
business has made a special request for that
option, and, if not, returning the default.

More precisely, here's the current design:

(I apologize for my Postgres-ish accent; I'm
afraid I'll introduce errors if I try to translate-
on-the-fly as I write this post.)

CREATE TABLE business (
business_id INT PRIMARY KEY,
&tc....
);

CREATE TABLE default_setting (
name TEXT PRIMARY KEY,
value TEXT NOT NULL
);

CREATE TABLE setting (
business_id INT NOT NULL REFERENCES business,
name TEXT NOT NULL REFERENCES default_setting,
value TEXT NOT NULL,
PRIMARY KEY (business_id, name)
);


Then I create a business_setting VIEW, that
encapsulates the logic to check the setting table
first, then the default_setting.

(Side query: I have that VIEW written two different
ways. The first way I wrote it cross-joined the business
and default_setting tables, and (left) joined the result
to setting.

The next way (credit goes to a mostly anonymous Joel on
Software poster) uses a UNION between a SELECT on
setting and a SELECT on default_setting where the setting
row doesn't exist.

Is there a good reason to prefer one version to another?
These tables are relatively tiny, and performance isn't
an issue. I find the UNION version more clearly expresses
the logic, but that may just be more of a reflection of
my comfort with multiple joins.)


Finally, I come to my real question:

The current design solves my problem, but I think it could
be better. The value columns are currently always stored
as text, but the actual values are only mostly text; sometimes
they're boolean or integer values. In fact, for a particular
setting name, only one of those three types can make sense--
we can never see (1, 'a_setting', 'true') and (2, 'a_setting', '11')
as rows in the settings table.

(Also, if a name is restricted to a boolean in the
default_setting table, then that restriction carries over
to the setting table.)

I want my data model to reflect this constraint--I've
already in production put a row into setting that looked
like (1001, 'uses_new_feature', 'ture').

What's my best plan to do this? The first ideas that jump
to mind are to either add a 'type' column to the default_settings
table and CHECK that the proper coercion is reasonable,
or to go to a six table model (default_setting_text,
default_setting_bool, etc.).

It is not at all clear to me how to weigh those two options,
or whether I've missed an even better plan. I'd very
much appreciate your comments.

--
Mark Jeffcoat
Austin, TX

Mark Jeffcoat wrote:
Yes, the UNION version does seem more natural because it expresses
exactly what you're looking for.

I think adding a type column is a good idea. However, instead of using
a CHECK constraint, use a trigger function.