The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

single row tables

A customer's system will contain some configuration values in a single row table that they will be able to manage themselves.

But I need to ensure the table contains one and only one row.

Lets see:
1) could remove privs for insert and delete
2) hard code a primary key as in

any other techniques ?
IT guy
Wednesday, March 12, 2008
Why not store the values as CSV or XML config in the file system? Just a thought.
Wednesday, March 12, 2008
Are you using a database that allows column level security?

If so you could just allow the update right on the non key column. Or you could create a table with two columns, name and value which is a little more flexible than having a column for each variable.
Wednesday, March 12, 2008
You're better off having a configuration table that has 'name' and 'value' columns. That way, you can add configuration parameters without having to alter the table.
NerdAtHome Send private email
Wednesday, March 12, 2008
name-value tables become a pain in the backside very quickly. As soon as you want a config value that would want to be a particular datatype (a date/timestamp) you're going to run into problems, and adding columns to the table is only going to cause problems if you have badly designed data-access
G Jones Send private email
Wednesday, March 12, 2008
@G Jones:
What kind of problems are you talking about, regarding data types?

Surely, all sensible databases allow type casting, so this is not an issue.
Odysseus Send private email
Thursday, March 13, 2008
> But I need to ensure the table contains one and only one row.

How about, your app only *cares* about one row in the table.

So, give that row the PRIMARY_KEY value '1', then always select it using  'where PRIMARY_KEY=1'.  That way, who cares if anyone adds any rows, they won't be used.
Derek Illchuk Send private email
Thursday, March 13, 2008
We do it the way Derek describes above. We have an integer primary key and the row in question must be set to "1". If someone else adds 200 extra rows we really don't care.

As for key/value tables, there are pro's and cons.

1) It is easy to "lose" values. If someone deletes one of the rows or changes a key it is usually not noticed and there is nothing inherrent within the schema to remind you what type of value it should be and what its default is. With column based configuration, the column is always there regardless of the data in it. You can at least always discover the default value and data type. This may seem like a silly issue but it has bitten us many times in real life.

2) Key/value tables are more difficult when managing "groups" of related configuration entries. For example, let's say that I want two configuration entries related to some URL and they are stored in two different value rows. The SQL is more complex to get those values. Also, you can end up requiring "groups of groups". So what if you needed to store configuration of a list of URL's each with two or more values that are related to each other? You get the point. Key/value tables are better at storing single valued configuration entries much like a .ini file is structured. If you need relational configuration with unlimited numbers of rows per group then a column/value based approach might be easier to manage.

1) You can add new configuration settings without having to alter the database schema. Just be sure to document it very clearly (see con #1 above).

I've used both approaches many times and both have their strengths and weaknesses. It all depends on the type of data that you need to store and how often it changes.
dood mcdoogle
Thursday, March 13, 2008
Provided your database supports check constraints and primary/unique constraints, combine the two:

 bar VARCHAR2(32),
 baz NUMBER,
 CONSTRAINT chk_id CHECK (id = 1),

INSERT INTO foo (id, bar, baz)
VALUES(1, 'OK', 42);
George Jansen Send private email
Thursday, March 13, 2008
+1 for Constraints. You can either check on the id as given in the example or do a SELECT COUNT(*) and reject if the count is > 1.
Thursday, March 13, 2008
+1 for using your database to store data and putting application configuration elsewhere.

Saturday, March 15, 2008
Don't give them table rights. Used stored procedures for access.
killing time on jos
Thursday, March 20, 2008
We do what Derek & dood talk about. Everyone other than sa get read-only rights. 

For us, this table contains information about system-wide settings that need to be available to the (2) user apps & the (15 at last count) services that we run in a full-up installation.  Most of our clients prefer this approach b/c they don't have to manage config files & the separate permissions management that requires.  YMMV.
a former big-fiver Send private email
Saturday, March 29, 2008

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz