DavidM

So if you don't have a lookup table, now you have to hardcode values into your app and the database. All because you don't want to join to a table? Seems kind of silly.

Not only that, but if the values are in the CHECK constraint, you have no way of enumerating what the 'allowed' values are.

Nice troll...

You're not a DBA are you? God I hope not.

Do you know what a data dictionary is?

Actually we create views for all our domains so that users don't have to. I'll have to give you an example as you have shown no lateral thinking

CREATE VIEW Genders
AS
SELECT 'Male'
UNION ALL
SELECT 'Female'
UNION ALL
SELECT 'Unknown'

Wow, that's harsh treatment. A little too harsh since you probably haven't read much by him to really judge whether he has lateral thinking, which shows you went straight to the ad hominem attacks for who knows/cares why.

Do you treat your coworkers in the same fashion? Probably not, but now dozens of people get to read your blog and form an opinion on your level of respect for other people. Not that you should care, but things like this can come back to haunt you.

With that said, there are several tradeoffs between lookup tables and domains. For instance, how can you attach additional meta-data to a type? Add extra columns to your view?

To outright say that one way is flatly better smells of religous intolerance.

Is it really harsh treatment Lavos? He thought it was silly and I responded in kind. His response of "because you don't want to join to a table" shows he has little understanding (is that too harsh?) of the difference between a type and a relation in a DBMS context.

"no way of enumerating" shows he did not think about it at all and is simply regurgitating his style of coding.

All the problems he had with post were bogus and I spelled that out. In hindsight a little to harshly, but I get like that sometimes.

Ryan can make 2 choices now. Either he can get offended (like you) and spread the word that I am wanker or he can think about my reply. I think (hope) he is man enough (or woman.. it is hard to tell the gender based on a first name these days) to fend for himself...

Were did I say one was better thaqn the others?

Ahh.. the religious intolerance retort.. enough said.

Would you like me delete my reply Lavos?

In the bag tonight: .NET: 9; Admin: 1; Blogging: 2; Dev: 7; Perl: 1; Rant: 5; Securoty: 1; SQL: 3; Tools: 2; Writing: 2; WTF?: 1; WILY: 3; XML: 5.
Line of the night:

Lavos isn't the only one scratching their heads and wondering why you went off like that, I'm sure.

Who knows the wonders of the human mind?
Sleep deprivation due to 2 kids, a shit load of documentation to do or just an arsehole? I'd say all three....

So in the interest of the moderation I have reworded my response to Ryan... I will leave the original reply there for future ammunition for my "enemies"....

<EDIT>
No I don't think it is silly. The developers obviously (to me anyway, I did have all the requirements) chose the wrong aproach as there where only 3 values in the "look up" table. Enumerating the domain values is done via our extensive data dictionary. We simply create virtual views of all domains and the developers are able to pull these values for there front end work.
</EDIT>

I think it would be really sad if there really were "enemies" looking for amunition.

I had a friend that wrote an email to a coworker that would make the word harsh a complete understatement. That email was sent in a very public way to most of the smallish company. The story of that email has gotten him a reputation around town and has affected the ability of other people to work with/hire him (it's a pretty small developer community here.)

I am just wanting to give friendly advice to relax, take a moment, and remember that this is more or less a public forum. It may be your particular corner of the commons, but it is still public.

Just a couple more notes, then I'll be quiet.

Mea Culpa, I read into your response that you thought the check constraint method was better. period. It probably had to do with the tone.

I wasn't offended, merely concerned. I read POE-News semi-regularly, so the things that actually offend me probably won't be brought up here :)

You can do whatever you want with your blog, even edit my posts to make me look like an idiot. (though I'd prefer it if you skip the last one. I don't need help in that aspect of my writing.)

"Ryan can make 2 choices now. Either he can get offended (like you) and spread the word that I am wanker or he can think about my reply."

Can I think about your reply and still consider you a wanker? :)

I have to admit I was/am a lookup table kind of guy, but I am slowly coming to realize I have some bad habits. I learn alot from your posts. Keep 'em up!

Justin,

Of course you can, I have been called worse and I suspect that I will be called that again. Hoenstly I don't mind, I try to learn, but a pet peeve of mine is those that can dish it but not take it.

Lavos,

You are always a voice of reason (and so is my wife, you have no idea how many times she has saved me from my temper.... think Dave versus 5 drunk guys.. I reckon I could have at least taken 3 of them but the other 2 would have kicked the shit out me.). I will never edit or modify any comments, although after reading your initial post I was tempted to delete my response to Ryan. But I was raised with the notion that you make your own bed, so I am always prepared to wake up in a dung heap.

As for relaxing I haven't got time! :-)

So to Ryan, run the folowing:

DELETE FROM DavidM WHERE CommentType = 'Wanker'

I'm hoping the row count is 1!!!

DELETE FROM DavidM WHERE CommentType = 'Wanker'

So I assume your view is ...

CREATE VIEW CommentType
AS
SELECT 'Wanker'
UNION ALL
SELECT 'Legend'
UNION ALL
SELECT 'Unknown'

"If you can't attack the idea, attack the man". Please don't do this, Ryan's a hell of a guy with a valid points and you essentially answered him by saying "because this is the way I do it".

Personally, I don't see the additional value in using a constraint like this, *IMHO* <-- troll-be-gone ;)

Why use a different method to effect virtually the same behaviour? I'm sure lots of people have used data dictionaries but what, in the general case, do you buy with the effort of implementing this?

Richard

Wow. What did I start?

First off, I wasn't trying to troll. I apologize if I offended anyone (especially you David). Looking back, my remarks do seem to be rather flippant. Mea Culpa. Maybe a few well placed smileys would have solved that. :) :)

And no, I am not a DBA. No intention of becoming one.

Using CHECK constraints on a table is not how I am used to coding (I do use them, but not often). I also wasn't simply 'regurgitating my coding style'. I just don't see how having CHECK constraints is any better then using lookup tables. I do know what a data dictionary is, and I see no reason why I need to create a view of allowed values for users when a lookup table would do that job just fine. Also using CHECK constraints and a data dictionary view creates more work. You now have to keep those two items in sync. Granted, if the data does not change much this does not become a problem.

Also, what happens when you want end-users to be able to edit and create types? In that situation you must make them relations (I could be wrong. If I am, feel free to correct me).

Again, sorry for causing a sh*t storm on your blog. I never intended to cause any problems. After reading your EDIT comment, I ran that DELETE statement you posted and all seems to be well. :)

Interesting debate. Seems like the primary argument for the elimination of the lookup table is peformance. Has anybody actually benchmarked the performance difference between lookup tables and CHECK constraints?

I could see CHECK constraints being faster when reads are many and inserts are few, since the CHECK is enforced only on input and the join on the lookup table would be carried out with every read. If the number of reads approximates the number of writes, it really becomes a question of what's faster -- a foreign key enforcement or a CHECK constraint? Again, I'd be interested in seeing some numbers as I don't know the answer to this question.

TimG, That looks about right (I'll take the "Legend" value as well)

Richard, It is a huge conceptual difference between the 2 methods. As I asked the developers, why have you turned a type into a relation? Hugely important difference that a lot of developers have no understaning of and hence make design decisions that can cause problems.

Thanks for responding Ryan. I'm writing an extension to this topic as we speak (plus an apology)..

Steve, from my experience, the performance is improved regardless of the read/write ratio. Consider that an insert and read both require physical IO (Yeah yeah cache) for the join but purely CPU on the check constraint. IO is the database bottleneck...

Innocent/Naive/Stupid Question: What about storage space? If you have a users table with a million rows and you store 'Female' rather than 2, aren't you wasting a whole lot of storage?

And where do you draw the line in terms of number of lookup options? For example would you create a CHECK constraint for a USStates table?

That's not a stupid question Oskar. Storage is increased without a doubt and would be a concern on a case by case basis.. About 10MB extra in your example.

A would certainly worry a lot a more than I do now if volumes rose to hundreds of millions...

Our largest is about 25, but personally I would not hesitate to go to 50 (or is 52?). Post Codes (Zip Codes) is another matter entirely!

I think most, if not all, of the lookup tables I have used in the past are not single-purpose, such as constraint-enforcement.

Look at the advantage pointed out by changing your GENDER column to accept values of 1, 2, or 3...searches based on a tinyint value are going to be MUCH faster than one based on a varchar(7) field due to its smaller profile on a page. Then you have a lookup table which contains the acceptable values and their descriptions. In other words, the lookup table enforces 3NF, which often improves performance and storage space.

crazyjoe,

1) An Insert will be slower with a FK constraint.
2) An Update on the column will be slower with a FK constraint
3) A SELECT that requires a JOIN to the lookup will be slower.
4) A SELECT with a WHERE will be faster using a number than a string.

1 out of 4....

"In other words, the lookup table enforces 3NF, which often improves performance and storage space."

That has me totally confused. How can a look up table "enforce" 3NF? I think you mean that if I have to store the "description" with the value then I would also have it in my "core" table as well. SQL Server cannot cope with multiple property types (UDF's). I'd (and do) use a "look up" for those... Yukon should sort that out nicely...

David,

Thanks for the critique. I am always interested in a learning dialogue.

I can't think of too many instances, apart from some sort of database which is tracking the population explosion, where you would have such a high volume of inserts into a unique "persons" table that using the lookup would slow you down that much.

Also, how often are you going to go back and change the gender of a particular person? You shouldn't be doing that many updates on the gender field...if you are, the problem is less with your data structure than with the dirty data getting put there.

So now we're down to 2 selects, once is faster, one is slower. For a column like gender, I don't think it's unnatural to assume that one could look at a report or list of results and determine the gender simply by a distribution of names to gender codes. Beyond that, having a key available on the report (in terms of a single select of the valid gender codes) would be fine.

As for the 3NF comment, I apologize for leaving the keyboard and coming back to try to finish my thoughts. It's common in some places I have done work to store codes and descriptions in the same table and moving the descriptions out to a lookup table is typically what I suggest doing. It was a backwards statement....enforcing 3NF often involves creating lookup tables, but creating lookup tables doesn't necesarily enforce 3NF :)

IMHO this debate boils down to two styles of designing databases. I'm not going to say who is wrong and who is right, because, in the end, it's a matter of preference.

Matt is certainly entitled to his opinion, and his approach undoubtably uses best practices from the relational model. Check constraints are the preferred method of domain model. Ryan's approach is also a valid relational design and, from a developer's prospective, is an easy way to create a metadata catalog that is not a maintenance nightmare.

Yes I know there is a domain-key normal form, but I certainly have never needed to implement it. I also know what a revlar is, but I've never used the term in conversation outside the classroom.

I am just a database developer, I don't claim to have any insights on what Dr. Codd would say at this moment. But personally, as long as it works, and it is good relational design, then ... why can't we all just get along?

Sorry for the confusion, I meant David, not Matt. And yes I do know that Dr. Codd has passed (the key the whole key and nothing but the key, so help me Codd!) ...

Paul,

There is no such thing as "best practice" for RM. It is either relational or it is not. It is either in 1NF or its not. The other NF's are to reduce the number of constraints required to stop update anolomies.

Domain key normal form simple states that a table is DKNF if all constraints can be expressed by key constraints and domain constraints... As soon as there is a table constaint (ie a cardinality constraint eg. Only 5 rows), then then table cannot be in DKNF. The biggest difference is that 5NF is always achievable while DKNF is not.....

It still baffles me why you would swap a domain constraint for another relation, a database constraint (FK) and a key constraint... 1 constraint versus 2 constraints plus an extra table....

The decision to use a check constraint or a look up table is a business issue not a technial one.

Factors include:
Nature of the data
Volatility
Speed of response required for changes
Business relationship between developers/dba/business

It is most unlikely that anybody will introduce a new Gender but there are other values that will change at short notice even if they are stable for years. For example tax classifications my seem cast in stone but when they change, they change now, with no regard to how long it will take to raise a change request on an IT department, which these days is probably not even on the same continent.

A neat old post :-).

Ironic how easy it is to tell the middle-tier programmers from the DBA in the comments ...

How can you tell? I mean, wich ones you consider middletierers and wich ones are true DBAs?

DBA

If I need to enumerate fields from the lookup table? ok, my question answered: lookup table.

If the check condicion is just a constraint to restrict values? ok, my questions answered: check constraint.

I don't like read in dictionary when it's not needed... I'm talking about contention, yes. :)

Thank you for so funny text!
and sorry for my bad english...

Javier

Last night during the meeting, during the discussion on User Defined Types in SQL Server 2005, I side-tracked...