Is it just me, or does Joe seem to avoid the actual questions that Tom has
asked?? Or have the questions been truncated in some way?
Why is the city example a bad one? It's something that every address that
is stored has, and can potentially contain many duplications.
What is the relevance of the SAN? The majority of addresses won't have
anything to do with the book trade?!
Where Joe does mention putting city, state and zip into seperate tables, he
doesn't explain what he uses to reference between tables? If he doesn't use
an Identity column, does he just use the city, state or zip as the key?
Doesn't this cause over-bloated indexes therefore affecting performance and
possibly fragmented indexes?
I'm not familiar with Joe Celko, or his work, but from the messages that
have been posted I still haven't seen any definitive reason to not use
Identity columns, and in my experience, as Tom mentioned in his email to
Joe, most developers I have also ever come across have used Identity
columns. I have about 8 years experience of database design, and building
systems, and I have never come across anyone who has had serious problems
with Identity columns. Maybe that is purely luck.
There have been lots of great reasons posted for using Identity Columns, but
not many for not using them and I guess at the end of the day, do what works
for you, but be aware of the potential pitfalls, and develop accordingly.
In my personal opinion, and from my experience to date, I will continue to
use Identity columns, and I will continue to recommend them.
> From: Tom Nunamaker <[EMAIL PROTECTED]>
> Reply-To: [EMAIL PROTECTED]
> Date: Wed, 12 Jul 2000 13:47:36 +0200
> To: [EMAIL PROTECTED]
> Subject: What are you using instead of IDENTITY...Part II
>
> Continuing the dialog with Joe Celko, here's more fuel to the fire.
> New questions raised by Joe's latest email:
> * Has anyone looked at ORM?
> * I personally love ERwin. What better alternative is there?
> * The next step is to ask Joe Celko if he ever recommends a
> surrogate key.
>
> Tom Nunamaker
> [EMAIL PROTECTED]
> =================================================
>
>>> If I have a table with repeating information, suppose a city field in an
> employee table for example, to normalize it you would break the city into a
> separate table. If I used the city name as the primary key for this "city"
> table, then it wouldn't accomplish anything to put it in it's own table. <<
>
> That is a bad example. Each city in a Personnel table (remember use a
> plural or collective noun for table names) is probably going to be part of
> a different address -- mailing address, his/her doctor's address or
> whatever. Try a Personnel table with columns called kid1, kid2, and kid3
> for his dependents. Clearly having a Dependents table is much better
> design -- it beats the heck out of requiring everyone to have three kids or
> to kill one if they have over-breed.
>
>>> I could create an INTEGER field in this city table and reference the
> integer in the employee table. That would certainly take up less space
> overall. Are you saying it's better to NOT to do something like this?
> Instead, just put the repeating city name in the employee table and forget
> the city table? <<
>
> I would use the SAN (Standard Address Number) if I were in the book trade.
> It is a number for all the bookstores and publishers in the US. This is
> because the set of addresses can be treated like an entity in that model.
>
> I am saying look at your model.
>
> If Addresses are a serious part of the business model and appear in
> multiple places, then I would also have a table for (zipcode, city, state)
> and assemble the addresses from this and the street address line. ZIP
> codes get changed more than people think.
>
> If the addresses are not reused much, then put them with the table to which
> they belong. What you save in space with a separate address code, you will
> lose in doing JOINs.
>
>>> I've 'faked it' using IDENTITY fields but I'm willing to use a better
> approach if I can. How do you handle situations like this? I see
> "identifier" fields in the book "Data Modeling with ERwin". I know LOTS of
> developers using it. <<
>
> I know a lot of developers who do it, too. I bill their employers $1500 to
> $2000 per day to fix that kind of code. Oh, ERwin is a bad tool. It has
> planar graph problems, cannot show constraints, returns only one schema,
> etc. You might want to look at ORM modeling methods.
>
> --CELKO--
>
>
> ------------------------------------------------------------------------------
> Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
> To Unsubscribe visit
> http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
> send a message to [EMAIL PROTECTED] with 'unsubscribe' in the
> body.
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
