Re: Inner Join Where Not Null

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 11/02/04 

Date: Mon, 1 Nov 2004 22:45:12 -0600

"Mike Labosh" <mlabosh@hotmail.com> wrote in message
news:%23W5TvvHwEHA.1392@tk2msftngp13.phx.gbl...
> > You might want to learn to write SQL instead of dialect, especially this
> > dialect. It has three different semantics in various products that
> > inherited it from Sybase.
>
> You lost me there. Would you care to expand?

Dogbert says it best when having this sort of conversation. Just wave your
hand and say "Bah!" This is the best way to do it using SQL Server.

>
> > Also, why do sooooo many column names end with "_key"? Surely, you
> > would never, never violate ISO-11179 naming rules and name a data
> > element by the role it plays in one particular location in your data
> > model, would you? And you would never, never use multiple names (like
> > "name" and "company_name") for the same data element.
>
> 1. I'm a developer, not a DBA, and I've never been on the same page with
the
> way you guys think up schema names :)
>
"Bah!" Just say "Bah!"

> 2. I didn't design this. I'm the mop-up-the-mess guy that they brought in
> after they fired the consulting company that loused up the project. It's
> honestly too bad there was no legal action taken.
>

Been there done that. I get to the point of me posting here in a minute,
really, keep reading.

> 3. At the risk of getting beat by the SQL cluebat, ....
> 3.a. Where can I go to a centralized place to read these "ISO-abcdefg"
> specifications?

I hope someone answers this. I am interested in the standards, but let's
face it, there are other accepted standards than the ISO standards that are
well accepted. Then there is the normal stuff people use for names. Bleh.

> 3.b. I know that the whole thing about surrogate keys and natural keys is
a
> hotly debated religious issue, but I like this design / naming convention.
> Each table has a primary key that is an identity column, whose name is the
> name of the table with a 'Key' suffix. Any table where there is a xxxKey
> column where xxx is the name of a different table, I immediately know is a
> foreign key. It deeply appeals to the developer in me, because I don't
have
> to memorize the data dictionary, just imply the name or meaning of a
column.
> That way I can turn out more code in less time.

I have to agree with you here. This is my standard too. It makes things
far faster and more straightforward.

> 3.c. In the religious arena, I'm sort of a moderate in that I believe
that
> blindly accepting either of the the "Every table must have a surrogate
> identity PK / Every table must use natural keys" views is flawed. We have
> several lots of two column resolver tables that live between a many:many
> relationship, except each of them has an identity key. why? that's just
> stupid.

The most important thing is the second part. Every table must have a
natural key. Otherwise you are in possible deep trouble. Use identities or
not, but make sure that at least one of your keys has some value as a key.
In your many-to-many resolution table, the two migrated keys from the many
tables is the natural key. It can be the primary key, but it needs to be
enforced via a unique key. You can have an identity surrogate key, or a
user created key, or whatever, but you need to have a key. I use an
identity key, just because if I have to use this key as a foreign key too,
then I still have the value of the single migrated column key.

For example, consider your CityTable:

> CREATE TABLE City (
> CityKey INT NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
> CountryKey INT NOT NULL FOREIGN KEY REFERENCES Country (CountrKey);
> Name NVARCHAR(30)

The following is legal:

insert into City (countryKey, Name)
value (1, 'Denver')
insert into City (countryKey, Name)
value (1, 'Denver')
insert into City (countryKey, Name)
value (1, 'Denver')
insert into City (countryKey, Name)
value (1, 'Denver')

Um, we can keep going all night. A simple unique constraint on CountryKey
and Name and you are protected from this situation. I see more problems
with stuff like this that just about anything else. 

-- 
----------------------------------------------------------------------------
Louis Davidson - drsql@hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services.  All other replies may be ignored :)