SQL table with a single row?

Question

What is the point (if any) in having a table in a database with only one row?

Note: I'm not talking about the possibility of having only one row in a table, but when a developer deliberately makes a table that is intended to always have exactly one row.

Edit:

The sales tax example is a good one.

I've just observed in some code I'm reviewing three different tables that contain three different kinds of certificates (a la SSL), each having exactly one row. I don't understand why this isn't made into one large table; I assume I'm missing something.

Answer 1

It can be useful sometime to emulate some features the Database system doesn't provide. I'm thinking of sequences in MySQL for instance.

Answer 2

For some things you only need one row - typically system configuration data. For example, "current sales tax rate". This might change in the future and so shouldn't be hardcoded, but you'll typically only ever need one at any given time. This kind of data needs to be in the database so that queries can use it in computations.

Answer 3

It's not necessarily a bad idea.

What if you had some global state (say, a boolean) that you wanted to store somewhere? And you wanted your stored procedures to easily access this state?

You could create a table with a primary key whose value range was limited to exactly one value.

Answer 4

I've seen something like this when a developer was asked to create a configuration table to store name-value pairs of data that needs to persist without being changed often. He ended up creating a one-row table with a column for each configuration variable. I wouldn't say it's a good idea, but I can certainly see why the developer did it given his instructions. Needless to say it didn't pass review.

I've just observed in some code I'm reviewing three different tables that contain three different kinds of certificates (a la SSL), each having exactly one row. I don't understand why this isn't made into one row; I assume I'm missing something.

This doesn't sound look a good design, unless there are some important details you don't know about. If there are three pieces of information that have the same constraints, the same use and the same structure, they should be stored in the same table, 99% of the time. That's a big part of what tables are for fundamentally.

Answer 5

Unless there are insert constraints on the table a timestamp for versioning then this sounds like a bad idea.

Answer 6

What is the point (if any) in having a table in a database with only one row?

A relational database stores things as relations: a tuples of data satisfying some relation.

Like, this one: "a VAT of this many percent is in effect in my country now".

If only one tuple satisifies this relation, then yes, it will be the only one in the table.

SQL cannot store variables: it can store a set consisting of 1 element, this is a one-row table.

Also, SQL is a set based language, and for some operations you need a fake set of only one row, like, to select a constant expression.

You cannot just SELECT out of nothing in Oracle, you need a FROM clause.

Oracle has a pseudotable, dual, which contains only one row and only one column.

Once, long time ago, it used to have two rows (hence the name dual), but lost its second row somewhere on its way to version 7.

MySQL has this pseudotable too, but MySQL is able to do selects without FROM clause. Still, it's useful when you need an empty rowset: SELECT 1 FROM dual WHERE NULL

I've just observed in some code I'm reviewing three different tables that contain three different kinds of certificates (a la SSL), each having exactly one row. I don't understand why this isn't made into one large table; I assume I'm missing something.

It may be a kind of "have it all or lose" scenario, when all three certificates are needed at once:

SELECT  *
FROM    ssl1
CROSS JOIN
        ssl2
CROSS JOIN
        ssl3

If any if the certificates is missing, the whole query returns nothing.

Answer 7

A table with a single row can be used to store application level settings that are shared across all database users. 'Maximum Allowed Users' for example.

Answer 8

Funny... I asked myself the same question. If you just want to store some simple value and your ONLY method of storage is an SQL server, that's pretty much what you have to do. If I have to do this, I usually end up creating a table with several columns and one row. I've seen a couple commercial products do this as well.

Answer 9

We have used a single-row table in the past (not often). In our case, this table was used to store system-wide configuration values that were updatable via a web interface. We could have gone the route of a simple name/value table, but the end client preferred a single row. I personally would have preferred the latter, but it really is up to preference, especially if this table will never have any sort of relationship with another table.

Answer 10

If your database is your application, then it probably makes sense for storing configuration data that might be required by stored procedures implementing business logic.

If you have an application that could use the file system to store information, then I don't think there is an advantage to using the database over an XML or flat file, except maybe that most developers are now far more well versed in using SQL to store and retrieve data than accessing the file system.

Answer 11

There was a table set up like this in a project I inherited. It was for configuration data, and the reason that was given was that it made for very simple queries:

SELECT WidgetSize FROM ConfigTable
SELECT FooLength  FROM ConfigTable

Okay fine. We converted to a generalized configuration table:

ID  Name  IntValue StringValue TextValue

This has served our purposes well.

Answer 12

I really cannot figure out why this would be the best solution. It seams more efficient to just have some kind of config file that will contain the data that would be in the tables one row. The cost of connecting to the database and querying the one row would be more costly. However if this is going to be some kind of config for the database logic. Then this would make a little bit more sense depending on the type of database you are using.

Answer 13

I use the totally awesome rails-settings plugin for this http://github.com/Squeegy/rails-settings/tree/master

It's really easy to set up and provides a nice syntax:

  Settings.admin_password = 'supersecret'
  Settings.date_format    = '%m %d, %Y'
  Settings.cocktails      = ['Martini', 'Screwdriver', 'White Russian']
  Settings.foo            = 123

Want a list of all the settings?

  Settings.all            # returns {'admin_password' => 'super_secret', 'date_format' => '%m %d, %Y'}

Set defaults for certain settings of your app. This will cause the defined settings to return with the Specified value even if they are not in the database. Make a new file in config/initializers/settings.rb with the following:

Settings.defaults[:some_setting] = 'footastic'