Database design for data items that only exist once

Question

Ok, so I want to create a database with all the data of the site. This is no problem however, for those items that exist only once, what should I do?

For instance, on the homepage, the intro text is changed by a user frequently, how to store this in the db? There are 6 additional items on the homepage that are change frequently by the user.

My thought was to create a table that has those 6 items as columns and the table would only have one row. Would this be ok from a database design point of view?

Answer 1

You could do it like that:

 table PageContent
 -----------------
 varchar(10) Key
 varchar(8000) Value
 varchar(30) PageName //use an index for this column

You could load all variables for a page like that:

select * from PageContent where PageName = 'StartPage'

And in your code you would retrieve some collection which you could use like that:

Show(pageContents["Title"])

Answer 2

Assuming the six items are all of the same type (in this case text that supplies content to web page) I prefer a design that has columns ItemName and ItemValue VARCHAR. That allows you to easily extend the scheme to seven items (or more) in the future without altering the database schema.

You may also consider keeping this material outside the database.

Answer 3

Doesn't sound efficient, as you'd have to get that row for all items. If what you're holding is text, or a blob, have each item in it's own row. If, as you said, there are frequent changes to the items, you'll essentially be updating the same row over and over.

Another consideration: you now have 6 items. Maybe tomorrow you'll have 8, 19, etc. You will then have to constantly alter the table. I therefore recommend a 2 column table ('id' or 'type' column, and 'content' column).

Answer 4

Basically, what you are hearing is one of two solutions: an EAV solution or using a single row table. The single row solution has the following advantages:

1. You can enforce data integrity rules on the values including referential integrity.
2. Similarly, you can enforce that a given attribute have a value (via the nullable attribute).
3. You are guaranteed when you query for the value that you will have one and only one row.
4. It is harder for someone to willy-nilly change the key value (the column name) and break your code.

Unless you are going to have many hundreds of columns, having dozens of columns makes no difference.

An EAV structure is definitely more dynamic. If this is going to support the site assembly it still the case that whenever an attribute is added somewhere code must be changed to use that new attribute. Further, with an EAV structure, you cannot guarantee that a value will exist nor that it will matching something coming from another table.

The big advantage an EAV system has as I mentioned before is that it far more dynamic. If your system is really to support many different types of sites and that the site designers of those sites might have unforeseen needs with respect to storage of data attributes, then the EAV structure would be better.