Best way to model a singleton in a relational database

Question

When designing relational database schema for web applications, I often find a case where I end up creating a table just to contain one row, and only one row. It feels like that is the wrong way to design it, but I can't come up with anything significantly better, or that is obviously "the right way to do it."

A recent example is a site that let the users manually control content on the home page. Well, there's only one home page. I created a table that had all the necessary fields to build the homepage, such as a text field for an area that contained descriptive text. A field to store the name of a big image file. Some foreign keys that point to articles that will be featured on the homepage, etc. It works, but it feels wrong to have a table with just one row.

In the past I've tried many other designs, such as allowing multiple rows in the homepage table and selecting one at random. I've tried adding a boolean field named "active" and selecting one of the active homepages at random. I've tried forcing only one row to be active at any given time in the application logic. I've tried not even making a homepage table and having all the other items, such as articles, to have boolean fields with names like featured_on_homepage.

In most cases I could build the homepage with a bunch of constants in a settings file. The main problem with the settings file is that it is under developer control. Because something like the contents of the homepage is something to be edited by the user, it has to go in the database.

On many sites, I don't have this problem because I can build things like the homepage with a query such as selecting the five newest articles. But when I have pages that are manually curated with strict requirements, it becomes tricky to model it in the database. But imagine you've got a photo table and an article table. The requirement is that the homepage will display exactly five photos, exactly three articles, and two blocks of arbitrary text manually controlled by the user. How do you model that in the database the right way?

Also, I have this modeling problem in many other cases besides just homepages. It's just the easiest and most generally applicable example I could come up with.

Answer 1

One simple approach would be to save the Home Page properties in a Properties table (or call it something else) that is made up of Name & Value columns.

HomePageProperty1 - UserValue1

HomePageProperty2 - UserValue2

It may not be an ideal solution, but it is simple and flexible. It also eliminates the table with one row scenario.

Answer 2

There is absolutely nothing wrong with a table with one row.

If this is part of your design, you should enforce it. Give the table an identity column, give it a uniqueness constraint, then add a column constraint to only allow a single value. This will ensure that nobody will be able to add a second row, which could be catastrophic if the SQL statements that read the table (understandably) lack a where clause.

If you start getting a large number of columns, you might be tempted to narrow the table and instead have one row per configuration item. This isn't the best idea because you lose type safety and validation. Also, you would lose forward compatiblity with multitenancy, which might be important to you. A better solution would be to rethink what your entities really are and have separate single-row tables for different entities.

Yes, I'm not only saying a single-row table is OK, but I'm suggesting you may want more than one of them.

Answer 3

It's not clear to me that you have a problem that you need to solve.

A single-row table is in no way a problem for the database itself, and allows you to apply data types and constraints to the data.

I'm not sure that you are trying to solve a real problem, to be honest.

Answer 4

It seems to me that you're using a database for something that should actually be stored as a file.

Your description reminds me a lot of Wiki pages, where users can edit the content. In Wikis, or at least in the implementations I've seen, the pages are persisted as files.

This helps you with other web details such as allowing your users to cache your home page, which comes practically for free if you store the pages as files, but you'd have to manually implement the logic for cache invalidation if you're building the page on every request based on content stored on the database.

In any case, I just want to make it clear that even though most of an application's persistent data is stored in a database, it doesn't mean that we must store everything there. Databases are just one of the tools of our trade. We must learn to make good use of as many different tools as we can.

Answer 5

You could create a table called STATIC_CONTENT and have columns for a key as well as the content, active/inactive trackers, etc. Then, create a row with a key of "HomePage" and in your homepage, load the static content for that key and display it. That way, when you have other static content (about page, contact page, etc.), you can add rows to that table.

Answer 6

Seems to me that you could've abstracted one level more. In the end a "HomePage" is a "Page". You can have different types of pages with the properties you needed. If you had sections in your site, you'd probably need a "SectionHomePage", which can very well work the same as a "Home Page".

The requirement is that the homepage will display exactly five photos, exactly three articles, and two blocks of arbitrary text manually controlled by the user. How do you model that in the database the right way?

Let's try it like this. I'm adding an ArticlePage table so you see how you can separate properties accordingly.

PAGES
Id
Title
Description

HOMEPAGES
PageId (FK)
PhotoListLimit
ArticleListLimit
TextBlockListLimit

ARTICLEPAGE
PageId (FK)
ArticleMainQuote
ArticleMainBody
ArticlePhoto1
ArticlePhoto2

That would work fine for me, would even work great for a big site.

Answer 7

There is nothing wrong per se in having a table with only a single row. If you only ever have one instance of a certain entity, then it might very well be the most natural way to represent this.

But selecting a row by random is wrong and bad. If your domain logic only expects a single row, it is obviously an error in the data if there are actually more. So you should figure out who or what is writing invalid data to the database and prevent them from doing so! Depending on the database, you could probably add a constraint to the table to prevent this from happening in the first place, e.g. allowing only a particular value as the primary key.

Answer 8

Just to add to Walter..

The property table structure should allow multiple data types.

Fileds:
PropertyName
PropertyTextValue
PropertyDateValue
PropertyNumericValue
PropertyBoolValue

Answer 9

To create a singleton table, create a table with a dummy primary key.

CREATE TABLE example (
  _ bool PRIMARY KEY,
  property1 int,
  property2 text,
  property3 date
);

Answer 10

The issue here is a broad stroke mismatch between the data you intend to store and how the used storage provider is intended to be used. As you touched on, tables are inherently built for bulk entry storage rather than unique singular objects.

That doesn't particularly mean it's wrong, it just means that you're not leveraging (i.e. wasting) a lot of the features that a database table entails.

This is where you need to consider whether what you're doing is right for you. There is no one-size-fits-all answer here.

If you're happy using several storage mediums, consider moving this singleton data to a more appropriate medium, e.g. an application file, config file, or similar. However, if it's important to you that the data is contained in a single storage medium, then just accept that your singleton data lives in the database. This is a matter of what matters most to you: storage efficiency or storage unification.

In case you start dealing with multiple of these singleton tables, however, I would recommend that you consider merging all of these into a single table. It's not uncommon to have an application configuration table in your database, and this is effectively what you'd be dealing with. For example:

ID	Name	Type	Value
1	MySingleton1	MySingletonClass1	{ "Foo": 123, "Bar": "abc" }
2	MySingleton2	MySingletonClass2	{ "Baz": { "Bat": "ZZZ" }, "Baf": 343 }

Rather than storing each singleton's properties in distinct columns, you can opt to serialize the data into a single string value, and then create a table that houses these string values. The Type column tells you what class the Value data should be serialized into, and the Name helps you find the correct data (you could drop the name if you're happy using the Type as the informal identifier of the correct data).

This at least cuts down on having more than one single-row table.

In the past I've tried many other designs, such as allowing multiple rows in the homepage table and selecting one at random.

I don't understand what purpose this serves in relation to the topic at hand.

I've tried adding a boolean field named "active" and selecting one of the active homepages at random.

Same comment as above.

I've tried forcing only one row to be active at any given time in the application logic.

This isn't impossible and sometimes has to happen, but this is the nature of the beast. Databases tend to consider each table row to have its own life cycle independent of other rows. That's just how it has been designed and is generally approached. You can do it differently, but it requires a non-standard approach.

Consider if there are ways around this using sorting, e.g. a date field whereby you inherently know that the most recently updated entry is the active entry. That way, you can edit one row without needing to alter all the others, which makes it easier to handle this sort of "group logic" where the state of one row impacts the other rows.
That being said, this approach is not always possible, and then you're going to have to write some custom logic to change the settings. It's not too hard though, something like this does the trick:

UPDATE MyTable SET Active = 0
UPDATE MyTable SET Active = 1 WHERE Id = @myParam

I've tried not even making a homepage table and having all the other items, such as articles, to have boolean fields with names like featured_on_homepage.

That's one way to do it. But I'm struggling to see how this fits in with the current question. You were talking about a singleton, and now you're talking about a homepage, where articles can be on the homepage or not on the homepage. Therefore, logically, there is more than one page (because where do articles not on the homepage live?), at which point you're no longer dealing with a singleton since there is more than one of them.

It seems to me that what you're looking for here isn't a singleton to begin with, but rather a set of pages, one of which is the homepage. Articles belong to a page (or multiple pages, YMMV), and one of the possible pages there is the homepage.
Even if you barely have two pages, i.e. homepage and non-homepage, that would still use the same data structure that you'd be using if you had hundreds of pages.