General Status codes Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

General Status codes Table

Author

Topic

jadibk
Starting Member

1 Post

Posted - 2004-11-03 : 00:06:52

Dear all,
I would like to get an advice concerning storing status code. Actually there is a status column per table.
My issue is that there can be the same status code for different table . For example in the invoice
table P=Paid,c=Cancelled and in the accounting table P=posted, c=closed.To solve this issue i have decided
to put a general status table with a numeric statusid and status description field . Then all the tables in
my system (approx 100) will reference this table to get the description. My concern is that is this a good decision ? and if yes will this create
a bottlneck by having too much read on the status table. I am using Sql server 2000/Windows 2000 server/.Net.

Thanks

jadibk

Kristen
Test

22859 Posts

Posted - 2004-11-03 : 01:07:24

We do this, works OK and I much prefer it to 100 separate status tables!

Kristen

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-11-03 : 01:19:44

I always recommend using numeric(smallint) codes for such; with a lookup table with the description of the numeric code.
Again there are two ways in this, one is what you are doing, to have one single lookup for every such flag in the project complex.


Ex : TABLE_COMMON_LOOKUP.
FLAG       DESC.
1          PAID
2          CANCELLED
3          POSTED
4          CLOSED

An other is to have a seperate lookup table for each related group of flags.
Ex :


T_PAID_CENCELLED
----------------
PAID_CENCELLED_FL      DESC
1                      PAID
2                      CANCELLED

T_POSTED_CLOSED
----------------
POSTED_CLOSED_FL      DESC
1                      POSTED
2                      CLOSED

The formar is easier to maintain, less number of tables.. but as the size grows it becomes pain from the programming and debugging view as the codes grow too many to remember.

The later, is far many tables, but makes programing and debugging a breeze. This is my personal favourite.

Hemanth Gorijala
Exchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-03 : 04:07:36

quote:
Originally posted by Kristen

We do this, works OK and I much prefer it to 100 separate status tables!
Kristen

As always one can answer "it depends"

See also this topic:

[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=40196[/url]

rockmoose

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-03 : 10:37:54

My view is this:

If all status code sets have different meanings, put them in different tables. if any two entities use the same set of status codes, then they can reference the same status code table.

The reason I do this is: I almost NEVER have a table that consists of just two columns such as StatusCode, StatusDescription. Surely those statuses mean different things to your application! A code of "A" means an entity is considered active, "D" or "I" mean it is not inactive, "D" means it has been deleted and should never to be shown on normal forms/reports, "P" means preliminary and allow changes, but "X" means it is finalized and is active, but no longer can be changed, etc.

What I do is make a list of these different "attributes" or "rules" for each status code, and then actually create columns for them and store this information in the Status Code table. These are often simple boolean (or bit) columns, indicating Yes or No.

Something like this:


Code    Desc              Valid?     Active?     Editable?     Hidden?
A       Active            Y          Y           Y             N
D       Deleted           N          N           N             Y
P       Posted            Y          Y           N             N
R       Archived          Y          N           N             N
L       Preliminary       Y          N           Y             Y

do you get the idea? put as many columns in here as you can -- it is the entire blueprint for your application and your reports! you won't have to say "select .. where Status ='A' or Status = 'P'" to return active rows -- you say "Select .. where [Active?] = 1". Then you can change status codes, create new ones, rename them, re-code them, whatever and everything still works. and your various status code tables have MEANINGFUL DATA in them ! and you can easily create a brand new status code, set the flags as needed, and not change 1 single line in your reports or in your apps!

This is just my approach, but I find it works very, very well. doesn't apply in all cases, but if you identify the different things you app "does" to each entity, and indicate for each status what is valid or not, and store it in your various Status tables, you have an extremely flexible, self-documenting database. And your apps and reports almost write themselves!

Try it out ....

- Jeff

Kristen
Test

22859 Posts

Posted - 2004-11-03 : 13:40:59

If I had some complicated status code that amalgamated a variety of "properties" like that I'd have it in a table like you describe.

However, we have loads of columns in our DB with simple constraints of "must be A,B or C" or "must be X, Y or Z". These we put all in one table:

ColumnName
Value
Description

(Where ColumnName isn't the column name, as such, in case it changes, but an "int" that represents that column, and any other column that needs the exact same picklist).

These are used in our application to provide handy "picklists" on any data entry screen that uses a column-with-a-picklist.

Kristen

spud
Starting Member

7 Posts

Posted - 2004-12-02 : 16:45:04

Kristen,
I am new to all this...would you mind providing 1-2 examples of sample data for the solution you describe below? Thanks.

J

quote:
Originally posted by Kristen

If I had some complicated status code that amalgamated a variety of "properties" like that I'd have it in a table like you describe.

However, we have loads of columns in our DB with simple constraints of "must be A,B or C" or "must be X, Y or Z". These we put all in one table:

ColumnName
Value
Description

(Where ColumnName isn't the column name, as such, in case it changes, but an "int" that represents that column, and any other column that needs the exact same picklist).

These are used in our application to provide handy "picklists" on any data entry screen that uses a column-with-a-picklist.

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-02 : 17:01:15

I found this blog interesting when it comes to handling "domain values"
[url]http://weblogs.sqlteam.com/davidm/archive/2004/02/18/907.aspx[/url]

rockmoose

spud
Starting Member

7 Posts

Posted - 2004-12-02 : 17:26:15

And where have you ended up in terms of your approach Rockmoose?

quote:
Originally posted by rockmoose

I found this blog interesting when it comes to handling "domain values"
[url]http://weblogs.sqlteam.com/davidm/archive/2004/02/18/907.aspx[/url]

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-02 : 17:56:11

Well, kind of "traditional" I guess.
I use one table for each domain ( set of values / lookuptable ) and set up FK relationships for all tables referencing those values.
For well defined domains CHECK constraints ensure that only valid values can exist in that table.

The database enforces all the RI.
When retrieving the data from the domains ( lookuptables ), you just have to know which one to pick,
which I find easier than having to know how to "filter" a codetable.

rockmoose

Kristen
Test

22859 Posts

Posted - 2004-12-03 : 01:50:32

Spud: Here you go:


CREATE TABLE dbo.MyLookup
(
    ColumnName varchar(100) NOT NULL,
    ItemNo int NOT NULL,
    Value varchar(100) NOT NULL,
    [Description] varchar(100) NOT NULL,
    PRIMARY KEY
    (
        ColumnName,
        ItemNo
    )
)

CREATE TABLE dbo.MyTable
(
    MyKey int NOT NULL,
    MyCol1 varchar(1) NULL,
    MyCol2 varchar(1) NULL,
    PRIMARY KEY
    (
        MyKey
    )
)

INSERT INTO dbo.MyLookup
SELECT 'MyCol1', 1, 'A', 'This is A' UNION ALL
SELECT 'MyCol1', 2, 'B', 'This is B' UNION ALL
SELECT 'MyCol2', 1, 'X', 'This is X' UNION ALL
SELECT 'MyCol2', 2, 'Y', 'This is Y'

INSERT INTO dbo.MyTable
SELECT 1, 'A', 'X' UNION ALL
SELECT 2, 'A', 'Y' UNION ALL
SELECT 3, 'B', 'X' UNION ALL
SELECT 4, 'B', 'Y'

SELECT	MyKey,
	MyCol1,
	MyCol1_Lookup.[Description],	
	MyCol2,
	MyCol2_Lookup.[Description]
FROM	dbo.MyTable
	JOIN dbo.MyLookup MyCol1_Lookup
		 ON MyCol1_Lookup.ColumnName = 'MyCol1'
		AND MyCol1_Lookup.Value = MyCol1
	JOIN dbo.MyLookup MyCol2_Lookup
		 ON MyCol2_Lookup.ColumnName = 'MyCol2'
		AND MyCol2_Lookup.Value = MyCol2
ORDER BY MyKey

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources