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 Friday, October 19, 2007 3:07 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782
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.

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.

You have an insanely complex and dangerous design. And you implemented this disaster in the worst code I have seen.

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!

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.

Here is a simple attempt to make it readable with a try add ISO-11179 names. I also took the time to edit the DDL, which you did not.

CREATE TABLE ReferenceLists
(magical_physical_locator_id INTEGER
IDENTITY(1,1) NOT NULL -- proprietary code
PRIMARY KEY, -- non-key crap!!!
foobar_name VARCHAR(50) NOT NULL, -- magic size!
generic_constant VARCHAR(25),-- a varying length constant?? Wow!
created_date DATETIME DEFAULT CURRENT_TIMESTAMP,-- audit data
modified_date DATETIME DEFAULT CURRENT_TIMESTAMP -- audit data
);

CREATE TABLE ReferenceValues -- metadata design flaw!!!
(magical_physical_locator_id INTEGER
IDENTITY(1,1) NOT NULL -- proprietary code
PRIMARY KEY, -- no it is not!!
list_id INTEGER NOT NULL -- the only thing you did right
REFERENCES ReferenceLists (magical_physical_locator_id),
foobar_name VARCHAR(50) NOT NULL,
foobar_abbreviation VARCHAR(50), -- magic size!
foobar_constant VARCHAR(25) NULL, -- magic size!
foobar_description VARCHAR(255) NULL, -- magic size !
obsolete_flag BIT DEFAULT 0, -- stupid, non-SQL nullable bit flag!!
created_date DATETIME DEFAULT CURRENT_TIMESTAMP, --audit data
modified_date DATETIME DEFAULT CURRENT_TIMESTAMP --audit data
);

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???

-- Proprietary trigger syntax!!!
-- Hey, why port code or make it easy to read??
CREATE TRIGGER Trg_UpdateReferenceValue -- prefixes on schema names?
ON ReferenceValues
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
--Non-standard SQL syntax!!
UPDATE ReferenceValues
SET modified_date = CURRENT_TIMESTAMP
FROM ReferenceValues AS R
INNER JOIN
INSERTED AS I
ON (R.id = I.id)
WHERE (I.modified_date IS NULL) -- audit data!
SET NOCOUNT OFF;
END;

This nightmare should have been implemented as simple lookup table for each code. Google up this article for detail:

http://www.dbazine.com/ofinterest/oi-articles/celko22


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #413009
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 02, 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 08, 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
Posted Sunday, October 21, 2007 11:39 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, April 13, 2014 2:31 PM
Points: 342, Visits: 1,780
Jonathon Prosper (10/21/2007)

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.



Jonathon, it is a little confusing as to whom you are speaking in that last paragraph since you mostly address the post by tymberwyld, although you mention "the article", which I wrote. Regardless, I think you touched on a subject that needs to be discussed, and that is the level of politeness and professionalism in these discussion forums. I do not mind that there are several responses to my article that very much disagree with what I was suggesting because, as you pointed out, everyone is entitled to their own opinion. However, I am a bit shocked at some of the pointedly rude, insulting, and down-right unprofessional comments made by a few people.


There is absolutely NO REASON whatsoever for anyone in these forums to be rude to someone else. We aren’t talking politics here, and even if we were it should still be polite. We are all database professionals trying to learn from one another and these forums -- as a means of information and idea exchange -- are a great way for people to learn. But it is a shame when people who are new to this learn that they most likely shouldn't ask questions or share a solution of theirs out of fear of being put-down for it. If someone is knowledgeable enough to know a better way to approach the problem, then it is quite easy to simply say: I would approach it in this way as it has these merits. For example, Joe Celko's first response to my article did just that. He stated that it should be a one-row table, or even a view, and that the CLR poses certain problems. Ok, fine. But I am really bothered by Joe's response to the post by tymberwyld in which he copies the code only to point out, line by line, how much he doesn't like it and also calls it a nightmare and some of the worst code he ever saw. Personally, whether anyone feels that way or not, it is simply not acceptable on any level for someone in these forums to speak to anyone else in that manner and tone. There is just no good reason for it.


Joe, given how well-known you are from writing various books on the subject and being widely regarded as a SQL expert, I would really think that you could use your wonderful insight into these technical issues to more positively teach others better way of doing things; yelling at someone and calling them stupid actually discredits both you and our profession by making us look like the stereo-typical techies that are lacking certain social skills. And once such an attitude comes out in these forums it is all too easy for others -- especially the one being yelled at -- to respond in kind and hence what was a productive discussion turns into immature insults. If someone posts code that appears hideous in your knowledgeable opinion, then rant and rave all you want in your mind, but when writing an actual response please be courteous. You have a lot of knowledge to impart upon others and it is great that you share that with us, but it is hard to listen to even a correct answer when the format the answer is given in is so harsh; I am not even the person being yelled at in this instance and I still don't want to take seriously any part of your response to tymberwyld, no matter how correct you may be, simply due to the tone and insults.


But, I am not singling out Joe since I have seen several posts in these forums that are also insulting and that just has no place here. We are supposed to be helping each other out, not competing for "I am better than you" points. By all of us being respectful of each other we will create a much stronger and more productive community. :)








SQL# - http://www.SQLsharp.com/
Post #413260
Posted Sunday, October 21, 2007 11:45 PM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, March 08, 2010 5:04 AM
Points: 135, Visits: 90
Jonathon, it is a little confusing as to whom you are speaking in that last paragraph since you mostly address the post by tymberwyld, although you mention "the article", which I wrote.


Yes I meant the post. My slip.


-- JP
Post #413261
Posted Sunday, February 10, 2008 12:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 12, 2008 9:55 AM
Points: 3, Visits: 8
Sorry to necro this thread, but I was looking for alternative solutions and came across this. Thought I'd throw my 2p in.

I've had this problem a lot recently. My solution was to create a single scalar UDF, with a CASE inside it, which I passed a constant name in to, ie:

@myValue = dbo.Constant ( 'asset' )

rather than the more verbose:

SELECT @myValue = Constants.Value FROM Constants WHERE Constants.Name='asset'

I don't like use of CLR for this to be honest and applying UDF in queries is imho a disaster for performance in many cases.
Post #453682
Posted Wednesday, February 13, 2008 3:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 12, 2009 7:51 AM
Points: 2, Visits: 6
Be cautious. Not all of these approaches optimize out the same.

We use an object factory that is tightly coupled to database tables. To simplify data access we have a single view that becomes in essence a partitioned table, called dbo.Object

This view looks something like this:

create view dbo.Object
as

select 1 as objecttype_id, ID, Description from dbo.object1
union all
select 2 as objecttype_ID, ID, Description from dbo.object2
union all
select 3 as objecttype_ID, ID, Description from dbo.object3
union all
select 4 as objecttype_ID, ID, Description from dbo.object4

Now to query against that view, you can simply predicate on an objecttype. If you check the IO statistics, it is only looking up against the one table, provided you query it properly. Don't bother looking at the Query Plan as it does not truly reflect what the server does to fetch the data.

use this to check io stats:
set statistics io on


--this query works properly
select * from dbo.object
where objecttype_id = 2


--this query does not, as it evaluates each table, but at least it doesn't scan them.
declare @jobtype int
set @objtype = objecttype.object1() -- a udf that returns a value
select * from dbo.object where objecttype_id = @objtype


--this query is closer, but it is creating a working table and is evaluating for every row
select * from dbo.object where objecttype_id = objecttype.object1()

--tried this, hoping that the deterministic decisions were based on the input param to output, but to no avail
--results were same as above.
select * from dbo.object where objecttype_id = objecttype.object1(0)

--Same result
select * from dbo.object where objecttype_id = objecttype.GetType('object1')

--this is the worst, as it scans each table
select * from dbo.object
where objecttype_id = (Select objecttype.object1())


Joining on a settings table is an option, unfortunately as we may be doing multiple joins in tables requiring these constants, we would have to do multiple joins on the settings table, which turns out to be very inefficient.

So until we have a better CONST struct in SQL we will be forced to hardcode values for some of our queries.
Post #455427
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse