11

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.

  • It's not at all clear what problem the single row table presents to you, other than that it in some way represents a waste of a table. Are you sure that you're trying to solve a problem you actually suffer from? – David Aldridge Jun 1 '17 at 17:06
10

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.

  • 1
    This approach works for multiple purposes. For example, I'll store schema version information, rotation pointers for things that need to be cycled daily, etc. The schema version info is important when diagnosing what went wrong. It's a reasonable way to ensure a patch got applied for a database-only fix. – Berin Loritsch Dec 17 '10 at 13:32
  • 2
    +1 - This the exact table layout that our Data Architect gave me for a similar purpose. – Ali Dec 17 '10 at 13:35
  • 3
    The problem with this approach is that you need to represent different variable types as different columns, and have logic to tell you which column to use, and which are compulsory. You can't define that a boolean value is boolean, or that a date is a date, or that a particular attribute cannot be null, or must fall within a particular range or meet a condition -- all of the things that are trivially simple with a single row table. – David Aldridge Jun 1 '17 at 17:05
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.

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.

1

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.

1

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.

1

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.

0

Just to add to Walter..

The property table structure should allow multiple data types.

Fileds:
PropertyName
PropertyTextValue
PropertyDateValue
PropertyNumericValue
PropertyBoolValue
  • With this, how do you guarantee that only one propertyvalue is set per row? It's easy enough to enforce it in the application, but what if someone then updates the database manually and sticks some data in there? – Apreche Dec 19 '10 at 15:06
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.