SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: The Case for the Surrogate Key
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

robvolk
Most Valuable Yak

USA
15679 Posts

Posted - 01/07/2003 :  09:03:56  Show Profile  Visit robvolk's Homepage  Reply with Quote
I think I used the term "sensible keys" in the *OTHER* thread. If your design process makes natural keys unwieldly to enforce, and you can't redesign the database, then don't use them. Again, DON'T JUST USE A SURROGATE KEY because it's the very first thing you think of; use them ONLY because natural keys are not a practical choice for your current database design (so far you've been doing just that...bravo!)

Go to Top of Page

Onamuji
Aged Yak Warrior

USA
504 Posts

Posted - 01/07/2003 :  09:28:06  Show Profile  Visit Onamuji's Homepage  Send Onamuji an AOL message  Reply with Quote
so is there a site out there that keeps commonly used database schemas available for others? like the common schema to map out an address ... ? would such a site be worth existing? sort of like they have common XML schemas ... I think that would be way cool... give dba's and developers a place to start for designing entities and data relationships ... might also help promote the natural case :)

Go to Top of Page

robvolk
Most Valuable Yak

USA
15679 Posts

Posted - 01/07/2003 :  10:02:16  Show Profile  Visit robvolk's Homepage  Reply with Quote
Something like that, if it exists, ultimately would only make database beginners even lazier than they are now. People would no longer DESIGN databases, they'd just use a template somewhere, and god help them if they need to modify it. There'd be zero motivation for anyone to actually learn about database design. MS Access database wizards anyone? If you really want something as a starting point, use one of these wizards. I'm serious.

To me, the only table design I've ever seen that's absolutely perfect is the phone book: name, address, phone number. Now THERE'S an existing template that's useful. People would learn more about database design by reverse-engineering the requirements and specs for the phone book, just from its layout and the data in it, and then creating it in Access or something (Access particularly)

Go to Top of Page

Onamuji
Aged Yak Warrior

USA
504 Posts

Posted - 01/07/2003 :  10:21:15  Show Profile  Visit Onamuji's Homepage  Send Onamuji an AOL message  Reply with Quote
is the same thing true for XML schema's then? or is that only to help standardize the layouts? (i think that would be more of a correct description)

so generally, you would say the first step to building a relational-model for a database is to analyze the data that is going to be put into it? what about the projects that have no data, but the business process model is available? do you create the data before the model?

Go to Top of Page

Robwhittaker
Yak Posting Veteran

United Kingdom
85 Posts

Posted - 01/07/2003 :  10:30:25  Show Profile  Reply with Quote
Being in the UK, the governement has XML schema standards for things like addresses, and the best thing is with the way XML works you only need to reference the address of the schema on the UK government web site.

Not sure what the american goverenment's site is like, but that is xml.gov

Go to Top of Page

Onamuji
Aged Yak Warrior

USA
504 Posts

Posted - 01/07/2003 :  10:31:08  Show Profile  Visit Onamuji's Homepage  Send Onamuji an AOL message  Reply with Quote
so I read Joe Celko's stuff on trees, still a little confused. After having design a couple tables already working in production I'm questioning the quality of them. They work, for now but for future reference I would like to have a solid base for designing them.

here's a real simple real example that we use (i'm normalizing best I know how and trying to use it to build a hierarchy) the example is of our departments' teams. each team name is unique. simple enough.

CREATE TABLE team (
name NVARCHAR(64) NOT NULL PRIMARY KEY,
enabled BIT NOT NULL -- just because sometimes the team will become inactive and the relating records cannot be deleted
)

now for the hierarchy, I usually would have put that into part of the team entity, i'm pretty sure that's what feels wrong, since the hierarchy does not describe the team its self. so now i would have a table like

CREATE TABLE team_hierarchy (
team NVARCHAR(64) NOT NULL PRIMARY KEY REFERENCES team(name),
parent NVARCHAR(64) NULL REFERENCES team(name)
)

each team may have a parent then, or they may be the root of that team. but that team cannot be used anywhere else, thus the data in team_hierarchy is only about the team_hierarchy.

am I correct? if so I could use this model to model our departments hierarchy as well...

i'm sure I'm going to run into the problem where the hierarchy of an entity allows the entity to be resused further down the chain or in another sub-hierarchy...

i'm assuming then that a self referencing key would suffice? can that be done (i'm going to try once i get my train of thought fininshed here)

see maybe i'm learning sometime :) thanks for all your patience and understanding ... sometimes things don't click right away :D

Go to Top of Page

robvolk
Most Valuable Yak

USA
15679 Posts

Posted - 01/07/2003 :  10:52:06  Show Profile  Visit robvolk's Homepage  Reply with Quote
The XML schemas are more of a least-common denominator approach (or most-common, perhaps) to form a "standard" baseline. Sort of like the phone book: as long as you provide name, address, and phone number, then other phone companies can make do with it (they HAVE to, they agreed it's the standard schema)

Realize that XML (schemas) are meant to TRANSFER data, between two or more DISSIMILAR structures. They were never intended to replace data STORAGE methods, except in the most highly specialized circumstances. It's a bad idea to design a database around the conventions of an existing XML schema, just to make it conform to that schema.

As far as having a model without data, well, SOME of the data has to be coming from an external source, it can't all be system-generated. If it were then you can use any design you want. Whatever is being modeled, whether there is real data for it or not, there will be enough attributes/columns required in the structure to guide the design of keys, relationships, constraints, and so on. If there isn't, then the business model is FUBAR and needs to be dumped or redone.

Go to Top of Page

valko
Starting Member

2 Posts

Posted - 02/05/2005 :  16:04:07  Show Profile  Reply with Quote
quote:
Originally posted by Page47

okay....

[quote]
But that falsehood aside, if you already have a column (or composite) that uniquely identifies a member in your logical design (the first key mentioned), why do you need to contrive/dream up/invent a second surrogate key to do the same job?


Exactly "if you already have a column (or composite) that uniquely identifies", but natural keys are data and as we all know (some by hard way like me) in real world data changes, therefore in real world applications, please use surrogate keys because they are generated by RDBMS, if you write any theoretical paper about relational theory of course use natural keys :-) or learn it hard way.

valko

Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 02/05/2005 :  17:50:17  Show Profile  Reply with Quote
quote:
Originally posted by valko
Exactly "if you already have a column (or composite) that uniquely identifies", but natural keys are data and as we all know (some by hard way like me) in real world data changes, therefore in real world applications, please use surrogate keys because they are generated by RDBMS, if you write any theoretical paper about relational theory of course use natural keys :-) or learn it hard way.


If You have had bad experiences using "natural" keys, and think that using "surrogate" keys would have solved your problems.
Then please post a description of the problem and what happened.

It's not improbable that a bad choice of keys + bad logical model + bad design was the culprit and not the choice between "natural" & "surrogate" keys.
It's not very informative or educational to just post a comment like "use surrogates iof natural keys because data changes", there is just no backing up of a blanket statement like that.

rockmoose
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  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.11 seconds. Powered By: Snitz Forums 2000