Re: 23,000 Tables?

cd2448 wrote:
> Hi,
>
> I wanted to ask if there are any potential hazards with designing a
> database application with 23,000 tables vs. 300 tables, but some of
> these tables will have up to 50 fields, within which a large number of
> NULLs are stored.
>
> We are migrating a system from an old mainframe to Oracle 10g and want
> to understand what we are getting into. What admin activities are we
> letting ourselves in for by having so many tables? it feels wrong, but
> i am not sure if the alternative with so many empty columns is a better
> alternative.
>
> thanks in advance for any thoughts people might have!
>
> rgds, chris.

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 -- Received on Wed Nov 08 2006 - 18:07:27 CST