The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Status/Static Table (Flyweight Pattern) ORM Hydration

I have a design question regarding the design of status tables (or static tables if you want to call them that).

Suppose I have a class that looks something like this. There may be other properties, but the basic idea is that this is an object who's primary purpose in life is hold some "status" text.

public class Status
    private string description;
    public string Description
        get { return description }
        set ( description = value }

One of the first things I'll want to do somewhere at the top of my program is define a few instance objects that represent my statuses:

Status failedStatus;
Status successStatus;
Status cancelledStatus;

Incidentally, I think this is actually a usage of the Flyweight Design Pattern.
Anyway, now I can say something like this:

SomeObject object = new SomeObject();
object.Status = failedStatus;

OK, here's the big question: Which of the following table designs is more preferable?

-- DESIGN #1
create table statuses
    id int not null unique,
    description varchar

-- DESIGN #2
create table statuses
    id int not null primary key identity(1,1),
    mnemonic varchar not null unique,
    description varchar

Design #1 is nice and simple: just an id and the text. Notice however, that I did not make id an identity (or auto-incrementing for non-SQL Server folks). This is because I want to be guaranteed that the id I have chosen to represent a particular piece of text is always the same. If I had made id an identity, then I may get different results the next time I run my insert statments to populate this table and then my code will be broken because it can't find my descriptions (or it finds a different description than it was expecting)

Design #2 allows you to have the nice auto-incrementing id as a primary key, but it adds this extra "mnemonic" field to help me identify the row that I'm trying to retrieve. The integer id's can be whatever they want, as the server will dish out whatever's the next available integer.

Which table design is better?
Jason Marcell Send private email
Thursday, October 11, 2007
This is an instance where practicality trumps design. In a common instance of such a problem, you want to tie a satic "key" to some text, but often want that key to be shared among other things (apps, services, reports) that may not even be written in the same language, or use the same libraries.

The auto-incrementing features of DBMSs are nice, and guarantee uniqueness. However, the problem you describe causes synchronization issues often.

IMHO: Design 2, while not a "purist" design, wins out in some instances.
jmjatlanta Send private email
Thursday, October 11, 2007
Design #2 is an example of using Surrogate Keys (  I think this is a great design in most cases -- b/c the surrogate keys have reliable values, it gives you a way to implement equals/hashcode on newly created objects.

I find that the best column type for the surrogate key is a blob, so that I can put actual executable code in it if I want to.
Thursday, October 11, 2007
BLOBS?  I have never thought of that.  Hmmmmm... interesting.  I could store my audio files recorded at 96 khz from my bitchin pro tools rig at home. 

I give this design 4 out of 5 air guitars.
daddy-o Send private email
Thursday, October 11, 2007

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz