RE: DDS field reference equivalent in SQL -- MIDRANGE-L

Definitely true.

I may have to re-examine my NUMERIC(19,5) idea if we ever deal with Zimbabwe. Their inflation is so bad that a loaf of bread is 550,000,000 million Zinbabwean dollars.

-----Original Message-----
From: Stone, Joel [mailto:Joel.Stone@xxxxxxxxxx]
Sent: Tuesday, October 30, 2012 12:07 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: DDS field reference equivalent in SQL

All points agreed.

One point - large numbers are not solely the result of expensive products. As organizations expand and become more globalized, many factors contribute to "price". For example we deal with grain and barges. A barge can hold hundreds of thousands of pounds of grain, and the currency can be dollars or shekels or yen. When multiplied out, a barge of grain priced in yen or pesos can be a large number.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Matt Olson
Sent: Tuesday, October 30, 2012 11:58 AM
To: 'Midrange Systems Technical Discussion'
Subject: RE: DDS field reference equivalent in SQL

What you are saying is proof that the database you are dealing with is not normalized. Most of our legacy DB2 databases are in the same boat and I feel your pain. I try teach our RPG developers here the benefits of the normalized structure and this forum exchange will serve as a perfect example for future training opportunities.

I know dealing with legacy and inherantly bad designs is difficult and causes these types of requests to come up, but you would not be asking these questions with a properly normalized database and a price and quantity field all being something like NUMERIC(19,5).

I don't know many places that need price to go beyond five decimal places, and past 9,999,999,999,999,999,999. If it did, you have the most expensive product on the planet, or the most quantity of any type of product on the planet.

Lastly, in your example CustID should only be changed in one table. The customer table. As you said you should have had a meaningless immutable key being the reference back to the customer table in your other tables (a good choice may be an integer unless you imagine you will have over 4.2 billion customers then you might opt for a 64bit integer, a lot of products nowadays use GUID for these types of immutable keys).

-----Original Message-----
From: Stone, Joel [mailto:Joel.Stone@xxxxxxxxxx]
Sent: Tuesday, October 30, 2012 11:36 AM
To: 'Midrange Systems Technical Discussion'
Subject: RE: DDS field reference equivalent in SQL

At every place I have ever worked, there are an abundance of projects dealing with field expansions. In my experience, most shops set up CustID as the customer number used by users. Years ago many set these fields as numeric, and have had to change them to alpha, or expand these types of fields.

And yes, as organizations grow, price and qty and other fields expand past what a developer anticipated 10 or 20 years ago.

Without a field reference file, and without using the "LIKE" in RPG to inherit field attributes, these things cost companies lots of money to expand.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Matt Olson
Sent: Tuesday, October 30, 2012 11:27 AM
To: 'Midrange Systems Technical Discussion'
Subject: RE: DDS field reference equivalent in SQL

Yes, CustID should be a meaningless primary key. Why would the datatype for PRICE ever change? If it did because your developers didn't have to foresight to design the price column to be a numeric field to enough decimal places? The future maintenance cost seems negligible in a properly normalized database structure.

-----Original Message-----
From: Stone, Joel [mailto:Joel.Stone@xxxxxxxxxx]
Sent: Tuesday, October 30, 2012 11:13 AM
To: 'Midrange Systems Technical Discussion'
Subject: RE: DDS field reference equivalent in SQL

Soooo - are you stating that you have CustID in only one table???

Even if you replace CustID with a meaningless immutable primary key, you would still have boatloads of fields such as PRICE that a reference back to a single place where one could inherit the attributes would reduce future maintenance costs significantly, don't you think?

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Matt Olson
Sent: Tuesday, October 30, 2012 11:05 AM
To: 'Midrange Systems Technical Discussion'
Subject: RE: DDS field reference equivalent in SQL

You might want to ask yourself why you would need such functionality in the first place. In a properly normalized database design you will not have duplicitive data in multiple tables.

Better get your developers up to speed with this: http://en.wikipedia.org/wiki/Database_normalization

-----Original Message-----
From: Stone, Joel [mailto:Joel.Stone@xxxxxxxxxx]
Sent: Tuesday, October 30, 2012 10:11 AM
To: 'Midrange Systems Technical Discussion'
Subject: DDS field reference equivalent in SQL

An important part of making field length changes easier on Iseries is to use a field reference file to inherit attributes from other fields & files.

Is there an equivalent in SQL?

Thanks

______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs Skyscan service.
For more information please visit http://www.symanteccloud.com ______________________________________________________________________
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.

________________________________________________________________________
This inbound email has been scanned for all viruses by the MessageLabs SkyScan service.
________________________________________________________________________

______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs Skyscan service.
For more information please visit http://www.symanteccloud.com ______________________________________________________________________
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.

________________________________________________________________________
This inbound email has been scanned for all viruses by the MessageLabs SkyScan service.
________________________________________________________________________

______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs Skyscan service.
For more information please visit http://www.symanteccloud.com ______________________________________________________________________
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.

________________________________________________________________________
This inbound email has been scanned for all viruses by the MessageLabs SkyScan
service.
________________________________________________________________________

______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs Skyscan service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________