Re: 23,000 Tables?

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 09 Nov 2006 08:43:49 -0800
Message-ID: <1163090629.835152@bubbleator.drizzle.com>

cd2448 wrote:

> Mark D Powell wrote:

>> [snip].
>>
>> While 23,000 tables is a large number the fact whether this is good or
>> bad depends on how it was determined that 23K tables should exist. The
>> SAP product has more tables than what you list.
>>
>> If the tables represent unique entities and nor just copies of the same
>> entity dedicated to a specific part of the business such as having a
>> part_master table for each plant instead of just having one part_master
>> table with a plant column so that one part_master can handle any number
>> of plants.
>>
>> Also there is no point in picking up your current application and
>> creating a table for every file that exists and just convering the
>> existing code to programs that run on the new platform. You are
>> spending a lot of money just to port your current set of problems
>> rather than develop a new system to meet your business needs going
>> forward. In other words these 23k tables should represent the business
>> data in a fully relational design done from scratch. Otherwise it
>> would probably be cheaper to stay on the current system.
>>
>> We have several table with more than 100 columns so 50 isn't that manny
>> so long as each column is truely an attribute of the entity that the
>> table represents.
>>
>> HTH -- Mark D Powell CPIM, CIRM, OCP --

> 
> Thanks Mark,
> 
> we are migrating a report system from the mainframe - each table
> represents one report type, with the columns representing meta-data of
> the reports themselves. each report has its own set of columns,
> although some reports coincidentally share the same set of columns,
> it's not set in stone going forward. my concern is that managing 23000
> tables could be a nightmare - but i am actually not sure why! all
> efforts to consolidate more than one report into the same table
> eventually brings us to a situation where a table has 40-50 fields, but
> a particular report can have max 16 (limitation of old system) - so
> lots of NULLs - hence my question what  is best.
> 
> We don't have the  option to normalize our database  as the new system
> works with a flat data model and cannot adapt to different model.
> 
> Thanks for your feedback! Chris.

Sounds like someone needs to take a refresher course in database basics with an eye on normalization.

23,000 table is outrageous and I'll stick my neck out and say that I am not all that impressed with SAP's design either.

If you are moving from mainframe to Oracle get someone that understands the concepts and architecture of Oracle (really really well as they will have to stand up to a room full of dinosaurs).

To recreate something archaic in a new product is roughly equivalent to rearranging the deck chairs on the Titanic.

If you need a reference to a good consultant in your area contact me off-line.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Thu Nov 09 2006 - 10:43:49 CST