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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Simple Data modelling question....
 Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 3

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 01/03/2003 :  11:25:12  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Rob -- thanks for the reply.

Again (for the last time hopefully):

I AM NOT SAYING ADD IDENTITY COLUMNS AS KEYS FOR A TABLE. I AM SAYING THAT IS IT PROBABLY NOT A GOOD IDEA TO USE A LONG, DESCRIPTIVE FIELD (THAT CAN CHANGE) AS A PRIMARY KEY IN YOUR TABLES.

In your example of states: YES, YES, YES, YES!!!! PLEASE!!!! USE THE STATE ABBREVIATION !! I CANNOT AGREE MORE!!!! Just do NOT use the State's name! use that char(2) field and not the varchar(20). DO NOT ADD A "StateID" FIELD FOR GOD's SAKE.

Is anyone getting this yet???? One person? Bueller?

Ah, what the hell, one last time: MY POINTS HAVE NOTHING TO DO WITH A SURROGATE VS A NATURAL KEY.

I should write a little song or catchy ditty that can stick in everyone's head.

Thanks again for the sensible reply, Rob.


- Jeff
Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 01/03/2003 :  11:58:44  Show Profile  Reply with Quote
mmmm.........i wonder does the old description of the "child's swing" problem come to mind here....a child wants a swing, and after many, many iterations of design by various experts....it's realised that a tyre hanging from a rope on a tree was all that was wanted!!!


remember line #1 of Jozza...."(~ 1 a week per client, about 10 clients)"
...doesn't sound like DB performance and/or DB size will be a big issue here!!!

sometimes we all get carried away with things....



Happy New Year guys!!!

Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 01/03/2003 :  12:02:14  Show Profile  Reply with Quote
Jeff ... stop shouting ... calm down.

Maryland is a state. MD is not a state. It is a contrived abbreviation ... a short representation of a state ... it is a symbol, that alone has no meaning, but represents a real thing in the real world. If you use MD as a primary key it is a surrogate key. If you use some StateID as a primary key it is a surrogate. If you use Maryland as a primary key it is a natural key.



Jay White
{0}
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 01/03/2003 :  12:03:17  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Andrew --

Good point -- i definitely generalized things quite a bit; I think we all did. It's still a good discussion, I suppose.

Maybe he really should just use Excel !

- Jeff
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 01/03/2003 :  13:20:27  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Page47 -- that post was a joke, right?

I guess I didn't make it clear yet that I am not debating surrogate vs natural keys, I should've pointed that out I guess.

one more time (everyone sing along):

I AM NOT SAYING ADD IDENTITY COLUMNS AS KEYS FOR A TABLE. I AM SAYING THAT IS IT PROBABLY NOT A GOOD IDEA TO USE A LONG, DESCRIPTIVE FIELD (THAT CAN CHANGE) AS A PRIMARY KEY IN YOUR TABLES.

Let's put it to music:

(verse 1):

(C) I not saying
(Am) that you should add identities as keys
(Dm7) Just that maybe
(F) it is not a good idea
(C) to use a long descriptive field
(Am) that can change
(Gsus4) as a primary key
(G7) to your tables

(chorus):

(C) Especially because
(Dm) you would have to store and maintain
(G) this long, descriptive name
(Em7) in every other table
(G) in your database that references other tables

(verse2):

(G) now, for simple reporting
(A) if you don't want to join to get the name
(Am) that's fine, but for most of us
(E) we need to use joins in our silly funky databases anyway, mmm yeah
(A) to get attributes and data
(G7) from the other tables .....

(chorus)

(cool guitar solo)



- Jeff
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 01/03/2003 :  13:22:58  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Page 47 -- feel free to answer or comment on my questions from the last post on page 1 of this thread as well.

- Jeff
Go to Top of Page

Onamuji
Aged Yak Warrior

USA
504 Posts

Posted - 01/03/2003 :  13:39:17  Show Profile  Visit Onamuji's Homepage  Send Onamuji an AOL message  Reply with Quote
Logically, the state name makes perfect sense. Maybe even physically it makes sense.

Logically, having a domain without a surrogate key is a good thing. For instance, an Order has a Status (OrderStatus) with the structure.

CREATE TABLE OrderStatus (Name NVARCHAR(128) NOT NULL PRIMARY KEY CLUSTERED)

Physically this may not be such a great idea. Specially if you work for people that are like the people I work for. Imagine one day application owner/manager says instead of the order status "Complete", he want it to read, "Fulfilled". Now imagine your table is getting 100-200 transactions per minute. If you use the natural key to do this change, you would have to schedule some downtime, with millions of rows to update and to make sure no orders get the new status and throw an exception during processing...

Now imagine that you had the wits to add a surogate key during the physical design phase. It could be an IDENTITY but it doesn't have to. You would simply update that one domain entity and boom, your application keeps on ticking.

Of course, all of us know that the logical design phase would be the optimal version of the database we work with. However, physical is much more real world than most logical designs will ever be. However, the logical design phase cannot be ignored or you run into poorly design databases.

Database design is sort of like security design. Start with the best approach and slowly work your way up to the point needed. With security its always best to allow no access and only give access when access is required. Database design is the same way, always best to have a logical design with perfect normalization and work your way to a usable design as needed.

The life cycle of the design phases is more important than which one is better. They are all required to have a scalable, maintainable, and speedy design.

Least this is how I approach my dba duties. This is not even to mention the moronic requests of managers that totally obliterate any chance of design the best system you can. Damn those that have more power than us DBAs... I the next president of the US should be a DBA... nominate Onamuji for president !

Go to Top of Page

robvolk
Most Valuable Yak

USA
15732 Posts

Posted - 01/03/2003 :  14:32:34  Show Profile  Visit robvolk's Homepage  Reply with Quote
quote:
Logically, having a domain without a surrogate key is a good thing. For instance, an Order has a Status (OrderStatus) with the structure.

CREATE TABLE OrderStatus (Name NVARCHAR(128) NOT NULL PRIMARY KEY CLUSTERED)

Physically this may not be such a great idea. Specially if you work for people that are like the people I work for. Imagine one day application owner/manager says instead of the order status "Complete", he want it to read, "Fulfilled". Now imagine your table is getting 100-200 transactions per minute. If you use the natural key to do this change, you would have to schedule some downtime, with millions of rows to update and to make sure no orders get the new status and throw an exception during processing...

Now imagine that you had the wits to add a surogate key during the physical design phase. It could be an IDENTITY but it doesn't have to. You would simply update that one domain entity and boom, your application keeps on ticking.
I'm glad you brought this up, it's yet another example of where a natural key would be better.

What exactly is the reason for the application manager to change the value from Completed to Fulfilled? What's the difference? Why change it if they mean the same thing? And if they DON'T mean the same thing, you can't change them anyway. It would only confuse people who are used to seeing Complete. You do everyone a disservice by having "the wits" (sorry, I feel a lack of wits is more appropriate here) to add a surrogate key that allows for a bullshit change to be made without any consideration for the consequences. Using the natural key forces the issue: "if you want this change it's gonna hit the server hard and maybe require overtime to complete. You never mentioned that you wanted such a feature and having it can compromise data integrity. Also, using this surrogate key would require a different kind of query for all the status reports, and it could adversely affect performance." It certainly wouldn't improve performance to add a surrogate key afterwards.

I know I'm gonna get savaged for this because "that's not how the real world operates, Rob. I work for these guys and I have to do what they say." That's lemming behavior, and if people follow that maxim they've got no business complaining about or criticizing their dipshit bosses, because they did not take the opportunity to correct or educate them. The only person who can improve or eliminate that situation is the DBA or DB designer, period.

I've HAD this argument with my bosses several times, and always put up a fight if I disagreed with them. I didn't always win, but they certainly heard my side of it. It's not a power struggle either; both of you want a useful, well-designed database. And he wouldn't have hired me if he knew everything himself. My boss knows if I disagree with him that I've got a good reason for it, and he has to show me why his idea is better. He would not know this if I kept my mouth shut and did the stupid shit without question; and god knows, you or I will NEVER get anywhere later on when the boss's idea FUBARs and we stand there saying "but you TOLD me to do it this way..."

Anyway, that's probably not here nor there, but I can't say that using a surrogate key to allow for your superiors to make unnecessary changes on a whim later on is a valid excuse. It's penny wise and pound foolish. We would all agree that these kinds or managers/directors don't know their ass from their elbow, and if you are forced to work with them, why not protect THEMSELVES from their stupid decisions?

Did this topic say "Simple Data Modelling Question..."? That sure didn't work out...

Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 01/03/2003 :  14:32:56  Show Profile  Reply with Quote
No, I am not joking.

quote:
I guess I didn't make it clear yet that I am not debating surrogate vs natural keys, I should've pointed that out I guess.

Actually you have made two things very clear. 1) You don't understand these terms and 2) You don't care to learn what they mean.

ok ... lemme first address your questions you are so concerned about. You've added all kinds of data to the original question without providing a schema for your imaginary world ... but I'll do my best to address you questions nonetheless.

quote:
1) useful reporting will not just show a client's name, but primary contact, address, salesman, location, client_type or something similiar, etc. Should all of this data be stored in each transaction? if not, we need to join to the client table and other tables for this data. We do not elimate joins by storing the client name in every transaction, unless it is extremely simple reporting.

Quite true. If you use a "client name" as a primary key, any relation will need that "client name" as its foreign key. Sometimes you will save yourself a join ... say you have a client table and an address table. If I ask for the address of "ABC client", if "ABC client" is the primary key of the client table and thus a fk in the address table, you don't need to join.

quote:
2) Other tables may need to reference clients, such as contracts, budgets, invoices, labor transactions, etc. Should the entire client name be stored in all transactions in all of these tables?

entire client name? I don't know ... the primary key of the client table ... yes. If the "entire client name" naturally, uniquely defines the entity ... then yeah.

quote:
3) We also have employees, departments, divisions, offices, employee_types, invoice_types, etc. Should we store the full text description of all these in all transaction tables? Each of these components, like clients, also has other attributes (other than names) that we will need to reference for reporting, calculating, etc -- joins to these tables will also be required.

again true ... you have to join ...

That felt like a waste of time.

Now let's get back to my question. I'm going to try to not use the terms 'natural key' and 'surrogate key', because you are blinded by those words and claim you aren't talking about that (even though you are).

create table jeff_country (
abbr char(2) not null primary key clustered,
state varchar(2000) not null unique nonclustered )
create table jay_country (
state varchar(2000) not null primary key clustered )

 
jeff_country.state and jay_country.state is a long varchar. In jay_country.state it is the primary key. You are singing and shouting about that you should not do it like jay_country ... rather you should do it like jeff_country. You make some valid arguements. You make some unsubstantiated arguements. You claim jeff_country is faster than jay_country. I want to know How Much Faster? Tell me its 5% faster. Tell me its 1 second faster. Tell me about subtree cost, reads, cpu cycles. Tell me about metrics. JUST DON'T TELL ME THIS WAY IS BETTER THAN THAT WAY BECAUSE ITS FASTER without saying HOW MUCH FASTER.

Someone once told me to never ever ever have your primary key / clustered index be of anything other than an int datatype, because its soooo much faster. I trusted them. Now, there are hundreds of PKs out there I wish I could have back after realizing that in the worst cases we are talking maybe a couple megs and a couple milliseconds. And in many places (especially with lookup tables) I could have had better performance (no join). I wish that bastard had quantified just "How much slower" we are talking.

Jay White
{0}
Go to Top of Page

Onamuji
Aged Yak Warrior

USA
504 Posts

Posted - 01/03/2003 :  14:46:59  Show Profile  Visit Onamuji's Homepage  Send Onamuji an AOL message  Reply with Quote
I totally agree with your tactics to make sure you express your self. I learned that really fast and now to so with an almost preacher-style approach. But your very right, we simply cannot win them all.

Maybe the status thing wasn't too good of an approach and you gave me one more argument for not coding administrative parts for that system (thanks Rob!) Now shoot me for this... what about datastructures for Departments, Employees, Teams (business units), and so forth. Natural keys would be complex to maintain. Department names change all the time, employees get married, and teams are renamed all the time as well. Not to mention the relationships between Departments and Employees, Teams and Employees, and Team resources, just to name a few.

It sounds to me that the normalized/logical design of a database does nothing more than make it complex to handle which ensures job security and a bigger pay check... Ehgads... I think I'm catching on...

Go to Top of Page

robvolk
Most Valuable Yak

USA
15732 Posts

Posted - 01/03/2003 :  15:13:48  Show Profile  Visit robvolk's Homepage  Reply with Quote
quote:
Maybe the status thing wasn't too good of an approach and you gave me one more argument for not coding administrative parts for that system (thanks Rob!)


The thing is, it's a lot EASIER to debate features and such during the design phase, and a lot harder to make necessary changes AFTER it's been rolled out to production. I think a lot of people simply go along with the boss or the crowd and not discuss alternative ways of doing things just to get the design part over with. That's a shame, that the design stage is considered a necessary evil to be endured. I've never once heard someone say "man, I'm glad we stuck with a bad design without considering other methods, because now we've got a ton of billable hours ahead redesigning this mofo." People don't mind working, but re-working is another story.
quote:
Now shoot me for this... what about datastructures for Departments, Employees, Teams (business units), and so forth. Natural keys would be complex to maintain. Department names change all the time, employees get married, and teams are renamed all the time as well. Not to mention the relationships between Departments and Employees, Teams and Employees, and Team resources, just to name a few.
Then don't use natural keys. I agree that something like that could be too problematic for natural keys to be effective. As long as you've made the case for WHY you chose the key you use, and why you're NOT using a natural key, you've done a proper design job. Anything that avoids the knee-jerk reaction to reach for a surrogate key in every table is a good step. And don't forget Northwind; as much as some people damn it, it's got an excellent mix of natural and surrogate keys and it's pretty clear to see why each type was chosen for each table.

Maybe we should introduce the term "SENSIBLE keys".
quote:
It sounds to me that the normalized/logical design of a database does nothing more than make it complex to handle which ensures job security and a bigger pay check... Ehgads... I think I'm catching on...
I know you're aiming for a chuckle here, but that first part IS NOT TRUE. That's the danger with offhand comments like that. Too many people who are already confused might take that seriously and never bother to consider exactly what kind of database they're modeling, and simply go the all-surrogate-keys-all-the-time-everybody-else-does-it route.

Go to Top of Page

Onamuji
Aged Yak Warrior

USA
504 Posts

Posted - 01/03/2003 :  15:26:57  Show Profile  Visit Onamuji's Homepage  Send Onamuji an AOL message  Reply with Quote
ok so use this damn script if you can to decide which is faster ... i think it is a wonderful example of both methods ... let me know what all of you think after you look at the execution plans... mind you I made up most of the data, and forgive any spelling errors..

SET NOCOUNT ON

/* NATURAL TABLE STRUCTURE */
CREATE TABLE NaturalCountry (Country NVARCHAR(128) PRIMARY KEY CLUSTERED, Abbreviation NCHAR(2) UNIQUE NONCLUSTERED)
CREATE TABLE NaturalState (Country NVARCHAR(128) REFERENCES NaturalCountry(Country), State NVARCHAR(128), Abbreviation NCHAR(2) UNIQUE NONCLUSTERED, PRIMARY KEY CLUSTERED (Country, State))
CREATE TABLE NaturalCity (Country NVARCHAR(128), State NVARCHAR(128), City NVARCHAR(128), PRIMARY KEY CLUSTERED (Country, State, City), FOREIGN KEY (Country, State) REFERENCES NaturalState(Country, State))
CREATE TABLE NaturalZipCode (Country NVARCHAR(128), State NVARCHAR(128), City NVARCHAR(128), ZipCode CHAR(5), Extension CHAR(4), PRIMARY KEY CLUSTERED (Country, State, City, ZipCode, Extension), FOREIGN KEY (Country, State, City) REFERENCES NaturalCity(Country, State, City))

INSERT INTO NaturalCountry (Country, Abbreviation) VALUES ('United States', 'US')
INSERT INTO NaturalCountry (Country, Abbreviation) VALUES ('Canada', 'CA')

INSERT INTO NaturalState (Country, State, Abbreviation) VALUES ('United States', 'New York', 'NY')
INSERT INTO NaturalState (Country, State, Abbreviation) VALUES ('Canada', 'Ontario', 'ON')

INSERT INTO NaturalCity (Country, State, City) VALUES ('United States', 'New York', 'New York')
INSERT INTO NaturalCity (Country, State, City) VALUES ('United States', 'New York', 'Rochester')
INSERT INTO NaturalCity (Country, State, City) VALUES ('United States', 'New York', 'Buffalo')
INSERT INTO NaturalCity (Country, State, City) VALUES ('United States', 'New York', 'Syracuse')
INSERT INTO NaturalCity (Country, State, City) VALUES ('Canada', 'Ontario', 'Toronto')

INSERT INTO NaturalZipCode (Country, State, City, ZipCode, Extension) VALUES ('United States', 'New York', 'New York', '10001', '3411')
INSERT INTO NaturalZipCode (Country, State, City, ZipCode, Extension) VALUES ('United States', 'New York', 'Rochester', '14607', '3211')
INSERT INTO NaturalZipCode (Country, State, City, ZipCode, Extension) VALUES ('United States', 'New York', 'Buffalo', '15832', '4521')
INSERT INTO NaturalZipCode (Country, State, City, ZipCode, Extension) VALUES ('United States', 'New York', 'Syracuse', '16839', '3322')
INSERT INTO NaturalZipCode (Country, State, City, ZipCode, Extension) VALUES ('Canada', 'Ontario', 'Toronto', '00123', '3221')

/* SURROGATE TABLE STRUCTURE */
CREATE TABLE SurrogateCountry (CountryID INT PRIMARY KEY NONCLUSTERED, Country NVARCHAR(128) UNIQUE CLUSTERED, Abbreviation NCHAR(2) UNIQUE NONCLUSTERED)
CREATE TABLE SurrogateState (StateID INT PRIMARY KEY NONCLUSTERED, CountryID INT REFERENCES SurrogateCountry(CountryID), State NVARCHAR(128), Abbreviation NCHAR(2) UNIQUE NONCLUSTERED, UNIQUE CLUSTERED (CountryID, State))
CREATE TABLE SurrogateCity (CityID INT PRIMARY KEY NONCLUSTERED, StateID INT REFERENCES SurrogateState(StateID), City NVARCHAR(128), UNIQUE CLUSTERED (StateID, City))
CREATE TABLE SurrogateZipCode (ZipCodeID INT PRIMARY KEY NONCLUSTERED, CityID INT REFERENCES SurrogateCity(CityID), ZipCode CHAR(5), Extension CHAR(4), UNIQUE CLUSTERED (CityID, ZipCode, Extension))

INSERT INTO SurrogateCountry (CountryID, Country, Abbreviation) VALUES (1, 'United States', 'US')
INSERT INTO SurrogateCountry (CountryID, Country, Abbreviation) VALUES (2, 'Canada', 'CA')

INSERT INTO SurrogateState (StateID, CountryID, State, Abbreviation) VALUES (1, 1, 'New York', 'NY')
INSERT INTO SurrogateState (StateID, CountryID, State, Abbreviation) VALUES (2, 2, 'Ontario', 'ON')

INSERT INTO SurrogateCity (CityID, StateID, City) VALUES (1, 1, 'New York')
INSERT INTO SurrogateCity (CityID, StateID, City) VALUES (2, 1, 'Rochester')
INSERT INTO SurrogateCity (CityID, StateID, City) VALUES (3, 1, 'Buffalo')
INSERT INTO SurrogateCity (CityID, StateID, City) VALUES (4, 1, 'Syracuse')
INSERT INTO SurrogateCity (CityID, StateID, City) VALUES (5, 2, 'Toronto')

INSERT INTO SurrogateZipCode (ZipCodeID, CityID, ZipCode, Extension) VALUES (1, 1, '10001', '3411')
INSERT INTO SurrogateZipCode (ZipCodeID, CityID, ZipCode, Extension) VALUES (2, 2, '14607', '3211')
INSERT INTO SurrogateZipCode (ZipCodeID, CityID, ZipCode, Extension) VALUES (3, 3, '15832', '4521')
INSERT INTO SurrogateZipCode (ZipCodeID, CityID, ZipCode, Extension) VALUES (4, 4, '16839', '3322')
INSERT INTO SurrogateZipCode (ZipCodeID, CityID, ZipCode, Extension) VALUES (5, 5, '00123', '3221')
GO

SET SHOWPLAN_ALL ON
GO

SELECT ZipCode.Country, Country.Abbreviation, ZipCode.State, State.Abbreviation, ZipCode.City, ZipCode.ZipCode, ZipCode.Extension
FROM NaturalZipCode AS ZipCode
INNER JOIN NaturalState AS State ON ZipCode.State = State.State AND ZipCode.Country = State.Country
INNER JOIN NaturalCountry AS Country ON ZipCode.Country = Country.Country
WHERE ZipCode.ZipCode = '14607'
GO

SELECT Country.Country, Country.Abbreviation, State.State, State.Abbreviation, City.City, ZipCode.ZipCode, ZipCode.Extension
FROM SurrogateZipCode AS ZipCode
INNER JOIN SurrogateCity AS City ON ZipCode.CityID = City.CityID
INNER JOIN SurrogateState AS State ON City.StateID = State.StateID
INNER JOIN SurrogateCountry AS Country ON State.CountryID = Country.CountryID
WHERE ZipCode.ZipCode = '14607'
GO

SET SHOWPLAN_ALL OFF
GO

DROP TABLE NaturalZipCode
DROP TABLE NaturalCity
DROP TABLE NaturalState
DROP TABLE NaturalCountry

DROP TABLE SurrogateZipCode
DROP TABLE SurrogateCity
DROP TABLE SurrogateState
DROP TABLE SurrogateCountry

Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 01/03/2003 :  15:47:58  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
One last try:

What do you think of this statement:

"A standard business process that assigns a unique, consistently formated client_id for each client results in a natural key."

If you disagree, then:

For a table of all phones in the world, are phone numbers natural keys? Somewhere, some business process produced a "next" phone number and assigned it to each phone. So it is natural or not? I guess not.

Are SS#'s natural keys? Unique, but some process came up with them. So, apparently not.

Street addresses? Nope, someone used some process to assign street #'s just based on an ascending or descending even/odd pattern per side of the streets. not natural.

People's Names? Nope, the last name is derived from the parents, and a "business process" (the parent's deciding) determines the full name. And they're not even unique at all!

Company Names? Nope, a "business process" meeting was held to determine that name, and again, they're not unique.

States or Abbreviations? Abbreviations are derived from the state name pretty consistently, so those are out. State names didn't come "naturally" out of the soil, some human got together to come up with those, so state names or abbreviations aren't natural keys either.

And so on.

There is a big difference between using a database generated IDENTITY -- which at no point, to continue beating this horse, did i recommend -- to define a client and a business process that assigns client ID's to clients.

And, honestly, if you can't see the advantage to having neatly formatted, consistent and short client IDs or employee IDs or DepartmentIDs or whatever to store in your databases, then I guess I must not be making enough money.

Jay -- I'm sorry you still aren't quite getting what I've been saying, but I am glad to see you couldn't disagree with any of my points. As for metrics, remember: computers aren't magical. They compare and read 1 byte at a time. The less bytes they compare or read or store, the better they perform. I gave some pretty clear metrics about disk space if you'll recall. And you told me yourself you tested joins of long varchar()'s vs. int's and that ints are slightly more efficient.

now, if you'll excuse me, I need to get drunk tonight!


- Jeff

Edited by - jsmith8858 on 01/03/2003 15:48:36
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 01/03/2003 :  16:26:15  Show Profile  Reply with Quote
Jeff,

I original replied to this post to give an alternative design for Jozza. I was trying to show that a one-to-one relationship would give better data meaning and integrity. Unfortunately the topic was competely derailed by a Surrogate key vs Natural Key debate.

Don't keep telling us you said nothing about natural v surrogate because YOU brought it up. I quote.. "Yikes. You really shouldn't use a long varchar() field as a primary key"

Do you read what you write.. at all?

You added absolutely nothing to the question at hand but decided instead to run off about the evils of natural keys. Apparently without you knowing! A great politician you would make.

So given that you are right about keeping the Client_ID, is there anything in the one-to-one design that you don't like (Except for the extra join but that is OK right?)

Onamuji,

At least you have given us some data to work with. Unfortunately you have loaded the queries with only one example. That being return all information about ZIP codes

I will ask for just 3 more.

1) Show me all the Cities that have a postcode starting with "3"
2) Show me all the Zip Codes for New York
3) Show me the Country Abbreviations for New York.


DavidM

"SQL-3 is an abomination.."
Go to Top of Page

Onamuji
Aged Yak Warrior

USA
504 Posts

Posted - 01/03/2003 :  16:31:51  Show Profile  Visit Onamuji's Homepage  Send Onamuji an AOL message  Reply with Quote
that I left up for you guys to do, who are truely interested in it... from what I played with... it seems the natural is sort of faster at some ends (less joins) but has more data... while the surrogate version is sometimes faster has less data requirements (size) but however more joins are required thus consuming cpu cycles... what if you just wanted to know the country for a zip code, obviously the natural version is faster since you do'nt have to join all the way back to get to the country... of course you could fill your surrogate database with tables that make short-cuts and only require one join but that elimnates the advantage you had on disk space... these are just my opinions on what i have observed/know about sql server... i may be wrong or not...

Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 01/03/2003 :  16:47:41  Show Profile  Reply with Quote
Onamuji,

Nice to see someone with the courage to accept/test ddifferent views.

The surrogate version will only be faster when non-keys fields are requested. Every other type of query will show that the natural key method is faster.

As we can see, speed is relative to the questions posed to the database.

DavidM

"SQL-3 is an abomination.."
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 01/03/2003 :  17:03:07  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
quote:

Don't keep telling us you said nothing about natural v surrogate because YOU brought it up. I quote.. "Yikes. You really shouldn't use a long varchar() field as a primary key"

Do you read what you write.. at all?

You added absolutely nothing to the question at hand but decided instead to run off about the evils of natural keys. Apparently without you knowing! A great politician you would make.



I stand by it. you shouldn't be using a long, descriptive varchar() field that can potentially change and may not be unique as your primary key. NOTHING to do with natural vs. not natural. ABSOLUTELY 100% NOTHING to do with it. How did I mention natural keys at all ???? not ONCE did I even bring it up until it was thrown at me.

Please sing my little song and read my posts again.

For the perhaps 5th time today:

"I AM NOT SAYING ADD IDENTITY COLUMNS AS KEYS FOR A TABLE. I AM SAYING THAT IS IT PROBABLY NOT A GOOD IDEA TO USE A LONG, DESCRIPTIVE FIELD (THAT CAN CHANGE) AS A PRIMARY KEY IN YOUR TABLES. "

I don't care if that varchar() is natural or random letters or even the client's favorite movie or book (is that a good candidate for a key?). How can I be more clear? Should I draw a picture? a little skit?

Joe: "Hi Jeff. I heard you don't like natural keys."
Jeff: "Not at all Joe, I just don't like storing long, descriptive fields that can change redundantly in multiple tables. Seems easier and more efficient to assign client_id's to clients than to store their name all over the place."
Joe: (gasps) "But that can mean we need to learn JOINS! "
Jeff: "Well, Joe, maybe if we upgrade the server and install SQL 2030 and go to months of training we'll be able to make those joins work for us!"
Joe: "I hope so, Jeff, that'd be great. Seems like a long shot though."
Jeff: "My idea is so crazy and unheard of, though, I think it just might work !"

(I'm running out of methods of communication ....)

Edited by - jsmith8858 on 01/03/2003 17:08:32
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 01/03/2003 :  17:43:24  Show Profile  Reply with Quote
quote:

I stand by it. you shouldn't be using a long varchar() field as your primary key. NOTHING to do with natural vs. not natural. ABSOLUTELY 100% NOTHING to do with it. How did I mention natural keys at all ???? not ONCE did I even bring it up until it was thrown at me.



It looks like we will have to go back to basics..

You had a natural key and now you are destroying it (NB: Not replacing it) for a another that is irrelevant to the data contained in the table ie surrogate. But apparently it has nothing to do with natural keys... . If it looks like a cat, walks like a cat then it is a cat.
See look what you have done.. I am now communicating in silly verse. Oh well at least I haven't been reduced to singing or speaking with imaginary friends yet....

Perhaps you meant to say "Using a long varchar field as your primary key provides reduced performance on joins, extra disk space and additional update difficulties when compared to integer type keys."

And you know what Jeff, if that is what you meant then I agree.
Please note that I have never said anything to the contrary.

DavidM

"SQL-3 is an abomination.."
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 01/03/2003 :  19:48:21  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Yeah!!! I knew we could do it!!!

Thanks for your help; I knew we both seemed too intelligent to be that far off!! (well, you, mostly, as for me ...... who knows!)

I was trying to figure out a way to incorporate maybe a puppet show or some abstract art into the the discussion, thank you for saving me the effort.

May have been entertaining, though...


(that could be my new career: the SQL guy who communicats via songs and puppet shows. I may be on to something!)

- Jeff

Edited by - jsmith8858 on 01/03/2003 19:52:03
Go to Top of Page

robvolk
Most Valuable Yak

USA
15732 Posts

Posted - 01/03/2003 :  20:15:23  Show Profile  Visit robvolk's Homepage  Reply with Quote
Coincidentally, this episode was shown fairly recently on a cable channel here:

http://www.startrek.com/library/episodes_tng_detail.asp?ID=68510



Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page | Next Page
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000