Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

A New (and Hopefully Better) Approach to Constants Expand / Collapse
Author
Message
Posted Tuesday, October 16, 2007 3:17 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:34 AM
Points: 6,259, Visits: 2,031
Jobob Smith (10/16/2007)
Does the constant HAVE to be stored in a table??

Would it not be easier to just have the UDF return the value? (it is constant after all)

as in:

CREATE FUNCTION fnGetConst(@cWanted INT)
RETURNS VARCHAR(5)
AS
BEGIN
DECLARE @RetValVARCHAR(5)

SELECT @RetVal = case @cWanted when 1 THEN 'boo'
when 2 THEN 'hoo' END
return @RetVal
END


In relational databases lookup tables are the way to go. Period.
For several reasons:
1. Flexibility
2. The engine is optimized to handle tables ( use statistics of the values etc...)
3. That is the way Relational databases are supposed to be handled.
4. Totally agree that the table will remain in memory most of the time and no disk access is incurred!

Cheers,



* Noel
Post #411485
Posted Wednesday, October 17, 2007 2:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, March 2, 2015 5:24 AM
Points: 207, Visits: 966
I would've thought a simple view would be OK for the previous example, and hard-code the "3" in it. This view can then be used everwhere, thus there should only be one hard-coded occurence of "3", (which is the minimum required - even constants hard-code the value in their definition!). Or am I missing something?

As for all other "constants", why can't you define it anywhere you like? They'll all require the hard-coding of something. A function for its name, (plus any parameters if you follow the article's suggestions), whilst tables need their name and any identifying column/value, (depending if it only has one row/multiple columns, or two columns/multiple rows).

Until SQL Server offers an actual "CONSTANT" keyword, then just use whatever's appropriate to your project, and stop moaning about everyone else's choices.

Paul H


Paul

Post #411581
Posted Wednesday, October 17, 2007 4:52 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 1, 2015 3:14 AM
Points: 64, Visits: 76
Well, I would have to agree that the article is a bit of a shameless plug in order to get us to download the cool stuff offa their site! But as far as I can see, the stuff I downloaded IS quite cool, so no harm done IMO, in fact saved me a bit of work! :P


Con mucho carino,

RiK Munoz

Post #411652
Posted Wednesday, October 17, 2007 2:37 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, February 12, 2015 12:49 AM
Points: 2,675, Visits: 701
constants don't belong in sql , set a view as a constant? god help us!

The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Post #411929
Posted Wednesday, October 17, 2007 8:42 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, January 14, 2015 8:59 PM
Points: 1,038, Visits: 445
I cannot see the benefit of invoking the CLR for such a trivial purpose. The ideas of a single row table/view with the constants as columns is probably best. Someone else has already rightly pointed out that a function that does not accept params and only has a simple return statement is easily handled by the SQL engine and doesn't result in table scans.

The other big problem I saw with the bit of the article I read was the author's claim that the .NET hashtable equivalent stores and returns varchars, which can IMPLICITLY be converted to ints, etc.... When you compare your int data in a table with the varchar result of this hashtable, you may have your nicely indexex int column being implicitly converted to a varchar... yuk. :)



Post #412014
Posted Thursday, October 18, 2007 1:53 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 8, 2010 5:04 AM
Points: 135, Visits: 90
I'd agree with post instructing everyone to hard-code the value into a variable at the top of your code although I would always do so by referencing a lookup table of some kind.

Despite the fact that the value in the id column in your lookup table will never change you may find that your need to use that value in your code will. If you need to stop using that constant and replace it with another you need to be able to identify all your procedures/functions/views that use it.

As an example, say a table that contains status information has the status "failed" split into three new values.

Eg. Your original status FAILED (1) is split into FAILED - Pending (5) and FAILED - Notified (6) and FAILED - complete (7).

Now you want to change all stored procedures that use the constant value of 1 and change it to 5 (Dont' ask me why you just do). How would you easily identify which of your stored procedures have used the old failed status of 1 if the only reference to the value in your stored procedures is a declaration followed a set statement of SET @Failed = 1?

It's for that reason i'd advocate looking up the value in a lookup table within your code as much as possible. It is a simple thing to see which objects rely on other objects in your database if you refer to it within your code. If you simply hard code a value then well....good luck when you need to find it later.

This ability to "find" where you have used constants could be achieved using functions but it is my preference not to do so.


-- JP
Post #412076
Posted Thursday, October 18, 2007 8:05 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, February 2, 2013 8:21 AM
Points: 283, Visits: 268
I think I'm in the boat of not agreeing with this. Although I do like bleeding edge stuff being a developer AND a DBA, this just doesn't jive. I use constants in another way.

All my Lookup tables are in one table (well, two if you count the parent table that is just there to categorize the values). The parent table ReferenceLists stores the distinct list of Lookup "Lists". The ReferenceValues stores all the values for each list. Each List has a constant and each value has a constant (optional). I use the Constant for the Lists all the time (which you'll see in the View Triggers). However, the Constants for the values are only used when needed. This strategy gives complete flexibility for adding new Lookup Lists where I can even allow end-users to create their own Lookups (in the case of a CRM application where users want to add their own User-Defined Fields and one of those Fields has specific values).

Table Scripts


CREATE TABLE [dbo].[ReferenceLists](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[Constant] [varchar](25) NULL,
[CreatedDate] [datetime] NULL CONSTRAINT [DF_ReferenceLists_CreatedDate] DEFAULT (getdate()),
[ModifiedDate] [datetime] NULL CONSTRAINT [DF_ReferenceLists_ModifiedDate] DEFAULT (getdate()),
CONSTRAINT [PK_ReferenceLists] PRIMARY KEY CLUSTERED ([ID] ASC) ON [PRIMARY],
CONSTRAINT [UIX_ReferenceList_Constant] UNIQUE NONCLUSTERED ([Constant] ASC) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ReferenceValues](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ListID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[Abbreviation] [varchar](50) NULL,
[Constant] [varchar](25) NULL,
[Description] [varchar](255) NULL,
[Obsolete] [bit] NULL CONSTRAINT [DF_ReferenceValues_Obsolete] DEFAULT ((0)),
[CreatedDate] [datetime] NULL CONSTRAINT [DF_ReferenceValues_CreatedDate] DEFAULT (getdate()),
[ModifiedDate] [datetime] NULL CONSTRAINT [DF_ReferenceValues_ModifiedDate] DEFAULT (getdate()),
CONSTRAINT [PK_ReferenceValues] PRIMARY KEY CLUSTERED ([ID] ASC) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TRIGGER [dbo].[trg_OnUpdateReferenceValue]
ON [dbo].[ReferenceValues]
AFTER UPDATE
AS
BEGIN
Set NoCount On
-- Update the ModifiedDate on any Rows where it wasn't specified explicitly
Update dbo.ReferenceValues Set ModifiedDate = GetDate()
From dbo.ReferenceValues R
Inner Join inserted I ON (R.ID = I.ID)
Where (I.ModifiedDate Is Null)
Set NoCount Off
END
GO

ALTER TABLE [dbo].[ReferenceValues] WITH CHECK
ADD CONSTRAINT [FK_ReferenceLists_ReferenceValues] FOREIGN KEY([ListID])
REFERENCES [dbo].[ReferenceLists] ([ID])
GO

Usage


Now, to make this easier on developers and me, I usually wrap views around these values for specific cases (although it could all be handled in code).

Let's say we want to store Address Types in the database. So we create an Address table and put AddressTypeID on it. Next we add a Foreign Key to the ReferenceValues table's ID column. We could also specify a Check Constraint that the value must only be one where the ListID = 1 ('ADDRESS_TYPE'). In order to maintain this list of values for AddressTypes, we create a nice little view for it.

Notice how the View doesn't expose the "ListID" column because it isn't necessary. This view should be used as a Table. So, the INSTEAD OF trigger looks up the ListID auto-magically (inserting it if neccessary).

"AddressTypes" View Script


CREATE VIEW [dbo].[AddressTypes]
WITH SCHEMABINDING
AS
Select
ID, Name, Abbreviation, Constant,
Obsolete, CreatedDate, ModifiedDate
From dbo.ReferenceValues WITH(NOLOCK)
Where ListID IN(
Select ID From dbo.ReferenceLists WITH(NOLOCK) Where Constant = 'ADDRESS_TYPE'
)
GO

CREATE TRIGGER [dbo].[trg_OnUpdateAddressType]
ON [dbo].[AddressTypes]
INSTEAD OF INSERT
AS
Set NoCount On

-- Determine the ListID
Declare @ListID Int
Select @ListID = ID From dbo.ReferenceLists Where (Constant = 'ADDRESS_TYPE')
If (@ListID Is Null)
BEGIN
Insert Into dbo.ReferenceLists (Name, Constant) Values('Address Types', 'ADDRESS_TYPE')
Set @ListID = SCOPE_IDENTITY()
END

-- Insert the Values
Insert Into dbo.ReferenceValues
(ListID, Name, Abbreviation, Constant, Obsolete,
CreatedDate, ModifiedDate)
Select @ListID, Name, Abbreviation, Constant, Obsolete,
IsNull(CreatedDate, GetDate()), IsNull(ModifiedDate, GetDate())
From inserted

Set NoCount Off
GO

You can also add other columns to the ReferenceLists table (ex. SequenceNo if you want to display values in a specific order).



Post #412225
Posted Thursday, October 18, 2007 6:41 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 8, 2010 5:04 AM
Points: 135, Visits: 90
Doesn't this overly complicate what should be a simple design.

If you want to store address_types then create a table called address_types and store them in there. If you need a lookup table for phone number types then create a table called phone_types and store them in there. If you start mixing 'types' of things in the same table then you have changed the table from being an entity of one thing to being an entity of many things and make a joke of what a foreign key constraint is supposed to be used for.

What happens in future when you want to add more detailed information for an adress type? You cannot simply add a column and insert the data because your table does not only contain address_types. On top of that if you did decide to create a new address_type table then making all the modifications to your original design would be time consuming and complicated.

All foreign key constraints used in a mixed lookup table can never be relied upon to be 100% accurate. You can keep adding check constraints in every table that uses your one lookup table but this once again complicates what should be simple.

I prefer the design of many different lookup tables each unique and unambiguous in their purpose. It allows for foreign key constraint checks to be 100% accurate (what could be more important than accurate data) while having the flexibility to expand the tables columns as you see fit.

If there is a concern that the number of lookup tables will exponentially grow and you wont know where anything is....I guess that is a different topic.


-- JP
Post #412512
Posted Friday, October 19, 2007 7:09 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, February 2, 2013 8:21 AM
Points: 283, Visits: 268
If there is a concern that the number of lookup tables will exponentially grow and you wont know where anything is....I guess that is a different topic.

And hence forth, you know why I created this...

Joe Celko (10/19/2007)
Let me be brutal about this. I can see one -- and only ONE -- thing you have done right in this non-relational schema. This thing is so bad I want to use it in a book on bad SQL.


Wow guys, pretty brutal. No constructive criticism? I used to have respect for you but obviously you just like to hear yourself talk. Apparently you think I am a naive database coder or something.

Yes, I've read your article before and it basically has no merit. I've read ALL the pros and cons on this design! You made the whole thing way too complicated plus your little example table design was completely flawed from the beginning. No one in their right mind would use the design you mentioned. For one there's no Primary Key on it. I'm sure you didn't notice because you were too busy trying to think up witty quips, but I store the Primary Key of the values, not some Magical number.

I only use these "reference" tables for simple lookup values (IDs & Descriptions). When the Lookup "Type" (i.e. AddressTypes) becomes too complex or merits additional schema, that is when I offload it into it's own table. Removing the current Foreign Key and a couple of update statements later, I can reapply the new F-Key to the new stand-alone lookup table. So, flexibility and future growth are accounted for.

There are two sides to designing. The Logical design says "Hey we need 600 lookup tables!". The Physical design says "Umm, they're all the same schema and they only store IDs and Descriptions". It doesn't make sense to me to store 30 tables with an ID and Description. To me, designing databases is about storing the most data with the least bit of space. Even if the single lookup values table had many values, I've never seen mine grow above 500 rows. Too little for indexing to be effective but all the same I do set some indexes. It's also just right for pinning in memory. If this design becomes too large, then it's time to reevaluate and re-read the 1st paragraph. Just because you do not understand the design doesn't make it bad.


You have no relational keys. Didn't you know that proprietary auto-numbering is NEVER a relational key by definition? All you have is an exposed physical locator, as if you were creating an index into a sequential file.


What are you yacking about "Proprietary numbering"? I didn't realize the IDENTITY function was no longer used!? In case you don't understand, the Constant column is ONLY used when necessary for the values. Taking AddressTypes for example, maybe one row's Constant would be 'MAIN' and another 'MAILING'. The ID should never matter to a programmer, T-SQL coder or anyone. Constants do not change but IDs may.

I've used this design for about 3 years now and never had a problem with it. I was able to consolidate over 30 "lookup" tables. Yes, there are Foreign Keys. Every table that uses this as a Lookup HAS a Foreign Key pointing back to the Value ID. Obviously one flaw of this is that it's possible that one ID from another List ends up in your column, but how often have I had it beat into my head that "Business logic doesn't exist in the database". So, if you're that stupid of a programmer to give a user a drop-down of ALL the values instead of just those that belong in that column, then shame on you.


Did you know that keeping audit data in a table is illegal? And that if it were not illegal, it is stupidly dangerous. Do you also keep the log file on the same hard disk as the DB? DUH!


Also, what crack pipe are you smoking, who mentioned anything about auditing? I manage all my auditing through a Service Broker which hands off the audit data to a separate database asynchronously so it doesn't block inserts / updates. The modified dates are used to ensure there are no concurrency conflicts when two users are attempting to update the same row. The trigger is in place in case some stupid programmer creates a Proc that doesn't specify the ModifiedDate.


Do you have any idea why the BIT and BIT VARYING data types were removed from SQL? They are assembly language and are a sure sign of design flaws.


Oops, you got me on one thing! A Nullable Bit field! Overlooked on my part. However, bits are very useful and should not be counted out. I guess if you were king then all FLAGS in every programming language would be removed as well? What happens when I need to store multiple Statuses for my Application Members (ex. LockedOut but Active)? I suppose you'd tell me to add another bit column? And when that changes to another status that needs to be combined into the first two? Another column?


Since the rest of the schema is soooo screwed up, you are trying to patch it with procedural code and you did it in the worst way. Use a trigger to replace a DEFAULT???

Apparently you have no idea what the trigger is actually doing. AND Default values are only inserted for new records. If you didn't notice, the Trigger is AFTER UPDATE. Again, you love to talk before you read.

I didn't just fall off the boat yesterday and I didn't wake up one day and think this would be a great idea. I've proven it's usefulness time and again over many years and I will continue to use it.

I wonder, since this design is so non-relational in your book, how I would love to see your proposed solution to a GIS database.



Post #413039
Posted Sunday, October 21, 2007 6:08 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 8, 2010 5:04 AM
Points: 135, Visits: 90

If there is a concern that the number of lookup tables will exponentially grow and you wont know where anything is....I guess that is a different topic.

And hence forth, you know why I created this...



It would be good to have the simplicity of your single lookup table with a simple design back end. Perhaps wrapping many lookup tables in a single access point through a view is an option but like I said that is a different topic. If the design works for you then it works for you.

On another note I do appreciate you sticking your head out and writing the article on what is a common database design issue. I'd hate to think that I had contributed to an environment where discussion was negative and not constructive. You have your opinion on your design and its merits and I gave a response. Let's leave it at that.


-- JP
Post #413234
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse