Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers

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.

share|improve this answer
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
    
+1 I've used something similar at other jobs. –  Scott Vercuski Dec 17 '10 at 13:53
add comment

Just to add to Walter..

The property table structure should allow multiple data types.

Fileds:
PropertyName
PropertyTextValue
PropertyDateValue
PropertyNumericValue
PropertyBoolValue
share|improve this answer
    
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
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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