Suggested architecture for associating code to db rows

Question

I have a situation where I have a bunch of rows in a database. For example, let say we have a table called ReportRendererType.

Rows might be:

• Line Graph
• Bar Graph
• Grid

etc.

I can use the database to store how particular users want to view particular reports. Eg. Frank likes the Profit Report displayed as a Bar Graph. It would be lovely if I could associate code with the entry in the db. In other words, it would be great if the LineGraphRenderer class knew that it was associated with row 1 in the ReportRenderType table. One trivial example of why this would be useful is that you could populate a dropdown list of ReportRenderType options, and then select a value and a Manager class could easily figure out how to display the report given the value selected from the dropdownlist. The question is then - how do you make the association between the db and that chunk of code.

There are lots of options:

A. When you make the LineGraphRenderer class, you could have a property ReportRendererType which returned the primary key of the associated row.

B. You could have a LineGraphRenderer return an enumeration which had a value of the primary key (C# allows you to do this by setting the hash code value explicitly) this gives static typing, and a single location for all the db entries.

C. You could have a ReportRendererType property on LineGraphRenderer which returned the primary key but retrieved the value for the associated row from a settings file. This would facilitate creating the associations, especially if someone else has different primary key values. For example, Company A might have LineGraph stored in row 1, but Company B might have LineGraph stored in row 2.

Unfortuanately all these ways also seem to have drawbacks. I'm wondering how this problem has been tackled by others, and if they've found some great ways of doing things that I'm missing.

Answer 1

I had a similar problem.

You could define an interface for the Renderer Class. Then you implement a Base Renderer (Line Graphe maybe) and all your other Renderer Classes. The you have a RendererFactory which has GetRenderer. Then you give that Factory Parameter and the Factory cann look at the DB which Renderer to use. If the Factory found nothing use Default Renderer.

Maybe somthing like this is an idea.

Answer 2

I suggest that you not associate code with database rows! This is quite contrary to separation of concerns.

To be honest, this is not quite the silliest idea I've ever heard; but it's pretty close.

Answer 3

I use option B extensively. This relies on two things:

1. The values are not subject to frequent change (this requires a code edit and re-build)
2. The database is properly normalised so that you enumerate the primary key of, for example, ReportRendererType which is used within the database as a foreign key (in your Customer table for example).

This works very well as you can reference the enumerated values directly in database queries and updates. If you plan ahead a bit you can also create the enumerated type names so that they can be easily displayed in a UI by splitting the text by capital letter. 'BarGraph' becomes 'Bar Graph' for example.

Depends whether you want your code that tightly coupled to your database or not. Personally I'm comfortable with it but others may not be.