Is it ok to store presentation data in the database?

I am to displaying rows from a database (SQL SERVER 2005) table in a webpage. These rows contain a statusID (foreign key) that is further defined by Status table (eg. id, name, modifiedDate).

The various statuses should display differently (lets say they simply have a different background color).

I am using php to query the database, and build the web page table. As I build the table I am going to apply a css class to an element based on the status of that row.

I have at least 2 options to do this:

1. Define code logic in the php to handle it, and if th开发者_如何学Goe statuses are changed in the database, I will have to change the code.

2. Store the "class" in the database and simply apply the class that has been stored.

The latter option seems better to me, but I am unsure if embedding presentational data into the database is a bad design choice. This will be the foundation on which I create several intranet utilities, and I'd like to start off on the right foot.

There's nothing wrong with storing any data in the database, including presentation data. If it helps you produce effective results, while writing less code, then it is a good practice. The thing you need to make sure of is that you don't mix your presentation logic with your database logic.

You can ensure that these concerns are separated by encapsulating the data for your presentation layer in the properties of an elementInfo object (for example).

Since it is a CSS class that you are talking about, this presentation data should be kept separate from the business data. So, while it is okay to store both presentation data and business data in the database, it is not acceptable to store them in the same table.

Update re: comment No, you should not add a PresentationClassRecord's Id as an FK on the business object. I made a sample of an approach to the db below. I called the DummyTable your business objects, and the rest follows specification. The most relevant part is the StatusPresentationAssignmentTable

 -----------------------------------------------
 DummyTable
 -----------------------------------------------
 Id      Name     SomeOtherDataField    StatusId
 PK int  varchar  int                   FK int

 -----------------------------------------------
 StatusTable
 -----------------------------------------------
 Id      Name     ModifiedDate
 PK int  varchar  datetime

 -----------------------------------------------
 PresentationTable
 -----------------------------------------------
 Id      PresentationType    Value 
 PK int  varchar            
 sample data:
 43      CssClass            prettyBackground

 -----------------------------------------------
 StatusPresentationAssignmentTable
 -----------------------------------------------
 StatusId    PresentationId 
 FK int      FK int

Now with two simple join clauses you can get the presentation data and it is completely decoupled from your business data. Your script could do something like check if the Status of the Dummy has any presentation assignments. If it does then it looks at the PresentationType, gets the appropriate function to apply the presentation-data to the presentation, and executes it. (You would need to have a function for each PresentationType that knows how to handle the value - something that could be encapsulated by something like function applyPresentationValue(presentationElement, presentationType, presentationValue) that calls a different function applyCssClass(presentationElement, value) if the presentationType == "CssClass" ).

The class itself isn’t really presentational data. It’s just a label for each type of status. You could, in theory, use it for lots of things other than deciding what colour the status should be when it appears on a web page.

When you combine that class with a given set of styles, then it’s presentational information. And you’ll be doing that in your CSS file, not in the database.

However, option 1 doesn’t necessarily require you to change your PHP code if the statuses in the database change. Your PHP could just generate the class name from the id or name of the status. Your CSS would have to change if the id/name of a status changed, but is that likely? Shouldn’t each status remain constant, with new statuses being added, if there are changes to the statuses the application needs to represent?

While you can store the css class information in the database, many content management systems do this, you are probably best making the status a part of the classname.

i.e. status = open, closed use php logic to generate the table rows and set the css class to status_{name} then any time you add a new status or rename it you only have to add/edit the css file, no php recoding required.

.status_open{background-color:green;}

.status_closed{background-color:red;}

I see advantages and disadvantages for storing this in the DB. Obviously it is convenient to have the class information there, but it is not really part of the application.

I would lean towards not storing it there and doing something in the presentation layer to handle it based on status. My reasoning is because, especially since you are creating utilities, that the data may be used via an API or something later where the class is pointless.