Oracle 8i and 9i PL/SQL Collections and Records


	Systems grow. Business strategy and requirements change course. Marketing invents new promotions. Management requests new metrics. The only real constant is change. Our data models, development processes and testing methods must accommodate rapid change.

	Unfortunately, the majority of us do not have such a setup. Especially with regard to the foundation of everything else, the data model. The data model is especially fragile. It needs to be designed correctly and flexibly from the first draft. If it is not, what occurs is something I call “mudballing.” The DBA or developer either cannot or does not dare alter the data model in significant ways. Instead, they just tack on yet another column to an already large, poorly modeled table, thereby adding another layer to their precious mudball they don’t dare peel or crack open. With time, these mudball tables take on a life of their own. Everything depends on them. No one dares remove anything from them “just in case.” Within 10 years, only 10% of the columns will actually be used, but there are so many layers, so many years of bad decisions that you just have to go with the flow or hope the data center burns down so you can start from scratch.

	On the RMOUG listserver in January, in fact, a poster mentioned a gigantic mudball table he had inherited that had 350 columns in it.

	Mudballing metaphor best represented by the fiddler crab, but I love the image of this little scarab carefully gathering and packing down his favorite stuff into another layer on his prized ball. When I hear of a mudball table or system, where the management isn’t willing to risk doing it right, cleaning up or starting over, but would rather live with all the poor decisions of the past, I like to close my eyes and think of this hard-working little beetle, carefully building and jealously guarding his ball. It makes me chuckle and helps me deal with the pain of working with such awful data structures.

	Records are designed to minimize the impacts of change. If you have mudball tables and dung beetle managers, records could be especially beneficial to you.

	http://www.quest.com/whitepapers/Using_PLsql_Records.pdf