Eric,

Don't consider this to be the definitive word on the subject, because I'm not a DBA. However, I recently worked on a project in which the designers attempted to enforce a data model that was completely normalized. It was also completely unworkable in a Remedy environment, and required massive changes.

Why? In addition to Adam's very valid point about historical data, there is the issue of performance. Instituting a Primary/Foreign key model endemic to a normalized database creates an environment in which far more transactions are required than would otherwise be necessary. Why? Because many more source forms are used than are necessary, and the PK/FK model makes it far more difficult to reference data sources. For instance, the location data, which in Help Desk is all in SHR:Location, would be broken down into Address, City, State, and Zip forms in a completely normalized environment. How would one do a Set/Push Fields - efficiently - in that type of environment? You'd have to have one Set/Push for each form. As you can see, far more *creative* use of Remedy workflow - and far more of it - is needed to perform what would otherwise be pretty simple tasks.

I think Remedy engineering has been looking at ways they might be able to make Remedy work in a more - though not completely - normalized environment. I don't know what they'll come up with, or when, or if they'll find something that's workable at all, but if they do, it'll make DBAs happier.

Rick Cook
Senior Remedy Consultant
Denali Advanced Integration

-----Original Message-----
From: Eric Day [mailto:Eric.Day@YESICS.COM]
Sent: Tuesday, December 31, 2002 5:12 PM
To: ARSLIST@ARSLIST.ORG
Subject: Database Normalization


A year or so ago there was a brief discussion on the list regarding Remedy
and database normalization ... Adam Wilkinson made a great argument against
normalization in the case of Help Desk (related to maintaining historically
accurate data).

I also vaguely recall a whitepaper that circulated detailing reasons why it
would not be good to normalize Remedy's DB. Does that ring a bell for
anyone? Do any of you have a copy of it you'd be able to provide?

Of course, I'd much rather be focusing on functional requirements ... but
if anyone has any other arguments against normalization, please post them!

Best regards,
Eric Day


You may control your subscription options, including UNSUBSCRIBE,and ACCESS THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send eMail to support@arslist.org


You may control your subscription options, including UNSUBSCRIBE,and ACCESS THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send eMail to support@arslist.org

Eric,
Not that it's any of my business, but I was just curious why you are looking
only for information against normalization. Wouldn't it be more prudent to
weigh the overall pros and cons of database normalization and then apply
what best suits your situation?

Scott Parrish

-----Original Message-----
From: Eric Day [mailto:Eric.Day@YESICS.COM]
Sent: Tuesday, December 31, 2002 8:12 PM
To: ARSLIST@ARSLIST.ORG
Subject: Database Normalization


A year or so ago there was a brief discussion on the list regarding Remedy
and database normalization ... Adam Wilkinson made a great argument against
normalization in the case of Help Desk (related to maintaining historically
accurate data).

I also vaguely recall a whitepaper that circulated detailing reasons why it
would not be good to normalize Remedy's DB. Does that ring a bell for
anyone? Do any of you have a copy of it you'd be able to provide?

Of course, I'd much rather be focusing on functional requirements ... but
if anyone has any other arguments against normalization, please post them!

Best regards,
Eric Day



You may control your subscription options, including UNSUBSCRIBE,and ACCESS
THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send
eMail to support@arslist.org


*****
"The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential, proprietary, and/or
privileged material. Any review, retransmission, dissemination or other use
of, or taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited. If you received
this in error, please contact the sender and delete the material from all
computers."


You may control your subscription options, including UNSUBSCRIBE,and ACCESS THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send eMail to support@arslist.org

Eric

I have taught Data Modeling and have spent many hours in discussing the
theory of a sound application data model with the pioneers of Relational
Data Base theory: Ted Codd and Chris Date. For the ten plus years that I
have been designing Remedy applications I have always started with an
application data model and have often had to defend the data model to
purists criticizing Remedy as not being fully normalized. As Rick Cook
points out the Shared forms provide the degree of normalization required in
a "Request based application". If the data model was fully 3NF then the
values in the request would change during the life cycle of the request. As
an example Joe User requests action related to item x. If the attributes
for Joe User or the item changes during the processing of the request in a
3nf model the next person to view the request would see the changes and that
would possibly change the ability to resolve the request.

Ricks example of the 3nf of the attributes in an address is "normalized to a
degree by table menus like a menu for States.

The bottom line is that Remedy applications data model are as "normalized"
as they need to be for performance and functionality.

I am sorry to say that many of the custom Remedy application data models
that I have encountered during the last ten years have not been well
designed and have contributed to poor performance, maintenance problems and
difficulty in upgrades. But I generally only get asked to come in when the
customer has a problem so I don't get to see the well designed applications
very often.

Ed Reynolds
Vice President QMX SUPPORT SERVICES INC.
Home/Office: 818-884-4013
Cell: 818-421-8946
Home FAX: 818-884-2025
E-Mail: EReynolds@QMXS.com


-----Original Message-----
From: Eric Day [mailto:Eric.Day@YESICS.COM]
Sent: Tuesday, December 31, 2002 17:12
Subject: Database Normalization

A year or so ago there was a brief discussion on the list regarding Remedy
and database normalization ... Adam Wilkinson made a great argument against
normalization in the case of Help Desk (related to maintaining historically
accurate data).

I also vaguely recall a whitepaper that circulated detailing reasons why it
would not be good to normalize Remedy's DB. Does that ring a bell for
anyone? Do any of you have a copy of it you'd be able to provide?

Of course, I'd much rather be focusing on functional requirements ... but
if anyone has any other arguments against normalization, please post them!

Best regards,
Eric Day



You may control your subscription options, including UNSUBSCRIBE,and ACCESS
THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send
eMail to support@arslist.org


You may control your subscription options, including UNSUBSCRIBE,and ACCESS THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send eMail to support@arslist.org

I try to do a *little* normalization. For example, if it seems useful, I'll
split up how I store information into several forms. For example, I have a
Customer Form that is used to store address/phone information so that it
doesn't have to take up space on the ticket itself. As a side benefit, I
can create an Address Book from that form now.

I just somewhat normalized the way we were storing departments and employees
- formerly all in one form, now in three (Employee, Department, and the
Employee-Department forms).

Normalize if it makes sense to do it. Other people gave good reasons about
when not to do it. The whole primary key/foreign key thing also doesn't
work so great - I usually will link things based on a textual name for the
users benefit, and hide the PK/FK fields (I usually use Request ID for
these).

-----Original Message-----
From: Eric Day [mailto:Eric.Day@YESICS.COM]
Sent: Tuesday, December 31, 2002 8:12 PM
To: ARSLIST@ARSLIST.ORG
Subject: Database Normalization


A year or so ago there was a brief discussion on the list regarding Remedy
and database normalization ... Adam Wilkinson made a great argument against
normalization in the case of Help Desk (related to maintaining historically
accurate data).

I also vaguely recall a whitepaper that circulated detailing reasons why it
would not be good to normalize Remedy's DB. Does that ring a bell for
anyone? Do any of you have a copy of it you'd be able to provide?

Of course, I'd much rather be focusing on functional requirements ... but
if anyone has any other arguments against normalization, please post them!

Best regards,
Eric Day



You may control your subscription options, including UNSUBSCRIBE,and ACCESS
THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send
eMail to support@arslist.org


*****
"The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential, proprietary, and/or
privileged material. Any review, retransmission, dissemination or other use
of, or taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited. If you received
this in error, please contact the sender and delete the material from all
computers."



You may control your subscription options, including UNSUBSCRIBE,and ACCESS
THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send
eMail to support@arslist.org


You may control your subscription options, including UNSUBSCRIBE,and ACCESS THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send eMail to support@arslist.org

If I remember correctly from the dbm class I took, one of the steps you take after designing a normalized db is to do the de-normalization that will make it perform.

-----Original Message-----
From: Rick Cook [mailto:rcook@DENALIAI.COM]
Sent: Wednesday, January 01, 2003 8:29 AM
To: ARSLIST@ARSLIST.ORG
Subject: Re: Database Normalization


Eric,

Don't consider this to be the definitive word on the subject, because I'm not a DBA. However, I recently worked on a project in which the designers attempted to enforce a data model that was completely normalized. It was also completely unworkable in a Remedy environment, and required massive changes.

Why? In addition to Adam's very valid point about historical data, there is the issue of performance. Instituting a Primary/Foreign key model endemic to a normalized database creates an environment in which far more transactions are required than would otherwise be necessary. Why? Because many more source forms are used than are necessary, and the PK/FK model makes it far more difficult to reference data sources. For instance, the location data, which in Help Desk is all in SHR:Location, would be broken down into Address, City, State, and Zip forms in a completely normalized environment. How would one do a Set/Push Fields - efficiently - in that type of environment? You'd have to have one Set/Push for each form. As you can see, far more *creative* use of Remedy workflow - and far more of it - is needed to perform what would otherwise be pretty simple tasks.

I think Remedy engineering has been looking at ways they might be able to make Remedy work in a more - though not completely - normalized environment. I don't know what they'll come up with, or when, or if they'll find something that's workable at all, but if they do, it'll make DBAs happier.

Rick Cook
Senior Remedy Consultant
Denali Advanced Integration

-----Original Message-----
From: Eric Day [mailto:Eric.Day@YESICS.COM]
Sent: Tuesday, December 31, 2002 5:12 PM
To: ARSLIST@ARSLIST.ORG
Subject: Database Normalization


A year or so ago there was a brief discussion on the list regarding Remedy
and database normalization ... Adam Wilkinson made a great argument against
normalization in the case of Help Desk (related to maintaining historically
accurate data).

I also vaguely recall a whitepaper that circulated detailing reasons why it
would not be good to normalize Remedy's DB. Does that ring a bell for
anyone? Do any of you have a copy of it you'd be able to provide?

Of course, I'd much rather be focusing on functional requirements ... but
if anyone has any other arguments against normalization, please post them!

Best regards,
Eric Day


You may control your subscription options, including UNSUBSCRIBE,and ACCESS THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send eMail to support@arslist.org


You may control your subscription options, including UNSUBSCRIBE,and ACCESS THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send eMail to support@arslist.org


You may control your subscription options, including UNSUBSCRIBE,and ACCESS THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send eMail to support@arslist.org

Here, there are developers who always go straight to the relational database modeling before starting to build remedy applications but they often forget to consider the right flow
of processes and the importance of well defined status to have a good workflow empowered by ARS to deliver notifications, escalations, actions, enforce permissions, etc.

I think it is a matter of balance, and to have the best of both worlds, but I tend to consider that the process is more important than the data model, although you still need to
think about tables and relations in terms of performance and maintenance.

Regards,
Mauricio

Ed Reynolds wrote:

> Eric
>
> I have taught Data Modeling and have spent many hours in discussing the
> theory of a sound application data model with the pioneers of Relational
> Data Base theory: Ted Codd and Chris Date. For the ten plus years that I
> have been designing Remedy applications I have always started with an
> application data model and have often had to defend the data model to
> purists criticizing Remedy as not being fully normalized. As Rick Cook
> points out the Shared forms provide the degree of normalization required in
> a "Request based application". If the data model was fully 3NF then the
> values in the request would change during the life cycle of the request. As
> an example Joe User requests action related to item x. If the attributes
> for Joe User or the item changes during the processing of the request in a
> 3nf model the next person to view the request would see the changes and that
> would possibly change the ability to resolve the request.
>
> Ricks example of the 3nf of the attributes in an address is "normalized to a
> degree by table menus like a menu for States.
>
> The bottom line is that Remedy applications data model are as "normalized"
> as they need to be for performance and functionality.
>
> I am sorry to say that many of the custom Remedy application data models
> that I have encountered during the last ten years have not been well
> designed and have contributed to poor performance, maintenance problems and
> difficulty in upgrades. But I generally only get asked to come in when the
> customer has a problem so I don't get to see the well designed applications
> very often.
>
> Ed Reynolds
> Vice President QMX SUPPORT SERVICES INC.
> Home/Office: 818-884-4013
> Cell: 818-421-8946
> Home FAX: 818-884-2025
> E-Mail: EReynolds@QMXS.com
>
>
> -----Original Message-----
> From: Eric Day [mailto:Eric.Day@YESICS.COM]
> Sent: Tuesday, December 31, 2002 17:12
> Subject: Database Normalization
>
> A year or so ago there was a brief discussion on the list regarding Remedy
> and database normalization ... Adam Wilkinson made a great argument against
> normalization in the case of Help Desk (related to maintaining historically
> accurate data).
>
> I also vaguely recall a whitepaper that circulated detailing reasons why it
> would not be good to normalize Remedy's DB. Does that ring a bell for
> anyone? Do any of you have a copy of it you'd be able to provide?
>
> Of course, I'd much rather be focusing on functional requirements ... but
> if anyone has any other arguments against normalization, please post them!
>
> Best regards,
> Eric Day
>
>
>
> You may control your subscription options, including UNSUBSCRIBE,and ACCESS
> THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send
> eMail to support@arslist.org
>
>
> You may control your subscription options, including UNSUBSCRIBE,and ACCESS THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send eMail to support@arslist.org


You may control your subscription options, including UNSUBSCRIBE,and ACCESS THE ARCHIVES at http://www.ARSLIST.org. For ARSList support, please send eMail to support@arslist.org