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.

"Constants" Views in SQL-Server

Hi all,

I am currently preparing the design of a smallish DB (<50 tables) in SQL Server 2005.

One nice trick I remember from old Oracle projects was the ability to use "static" views to create small sets of constant values.

Suppose you have a set of Status Codes for the entities of your DB. You want a neat design, so you put the codes in a table, with a proper ID and a human readable code:

PK        CODE
00001      OPEN
00002      WIP
00003      VALIDATED
...
00009      ERROR

Now, you know that the codes are pretty static, especially because in case you add a new one, you'll have to add code to manage it, so making the table contents modifiable by an admin console makes little sense.

You can add an initialization script to populate the table for you, obviously... but an alternative would be to define a view like this:

SELECT    1 AS StateId, 'OPEN' AS Expr1       
UNION
SELECT    2 AS StateId, 'WIP' AS Expr1
UNION
SELECT    3 AS StateId, 'VALIDATED' AS Expr1
UNION
...
SELECT    9 AS StateId, 'ERROR' AS Expr1

Now... any problems with this approach? Performance wise for example? Also, is it possible to force some kind of referential integrity between the StateId of a *real* table with the StateId returned from this View?

TIA (and please understand I am fairly unexperienced as a DBA, especially with Microsoft technologies).
Paolo Marino Send private email
Thursday, April 20, 2006
 
 
I don't understand what the view does that the table of codes doesn't do.
David Aldridge Send private email
Thursday, April 20, 2006
 
 
Nice trick! I didn't think about that. It's exactly today I wrote a class that populates the constant tables from my C# enums each time the application (ASP.NET) starts, so I don't need to worry about populating them when deploying. Great tip!
smalltalk Send private email
Thursday, April 20, 2006
 
 
That is a nice idea.
The immediate benefit that springs to mind is the ability to do a schema-only (no data) dump and have one less 'constants' table to re-populate for a clean distribution.
G Jones
Thursday, April 20, 2006
 
 
okay, you're saying these are static values, yes?

And you're also saying that they will so rarely be added to that you're not providing an interface to manage them?

Then don't select constants in your view. Use the unique, primary key ID column you already have.

Since this is a static table, a lookup table, make a prepopulation script to insert the values, including the ID value (look for "SET IDENTITY_INSERT" in SQL books online).
D in PHX Send private email
Thursday, April 20, 2006
 
 
looking at your example more...

Try to understand that the view you give as an example...all those values are just static.

This is the entire opposite reason for having a relational database.

There is no 'referential integrity' as your view is static text.

You're making a table for the values because you want them to intelligently relate, IMHO you're painting yourself into a corner.
D in PHX Send private email
Thursday, April 20, 2006
 
 
One thing that SQL Server 2005 has is User Definable Types.  You can make your own datatypes that can be used to define fields.  Here is an article on them http://www.devx.com/dotnet/Article/22644
SteveM Send private email
Thursday, April 20, 2006
 
 
WTF?

Why not just create an actual table and be done with it?
Sgt.Sausage
Thursday, April 20, 2006
 
 
Well, here I am trying to be civil and the Sgt. comes right out with it :)
D in PHX Send private email
Thursday, April 20, 2006
 
 
The main "advantage" is to have values explicitly part of the definition of the "table" itself.
Whenever possible I try to have stuff defined in one place and one place only.

Can someone address the main question? Is it possible in SLQ-Server 2005 to enforce referential integrity to a *view* instead of a *table*?

Thnaks...
Paolo Marino Send private email
Friday, April 21, 2006
 
 
No, AFAIK.
smalltalk Send private email
Friday, April 21, 2006
 
 
Furthermore I doubt that the database is aware that Expr1 and StateID are unique, nor of how many rows are in the "table".
David Aldridge Send private email
Wednesday, April 26, 2006
 
 
Stupid idea.

What is it with you programmers? Why do you always think you can improve on crappy relational databases?

The relational model and approach might not be perfect, but it's the best we've got.

Now will you all stop dicking around, trying to bypass it. Tables, referential integrity, the normal forms and all that tedious stuff. It works. And the better you use it, the better it works.

If you've got such a problem with RDBMS then just use a frigging text file to keep you information in.
Database purist.
Wednesday, April 26, 2006
 
 

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

Other recent topics Other recent topics
 
Powered by FogBugz