× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. October 2012

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
______________________________________________________________________

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.