Re: 23,000 Tables?

On Nov 9, 7:28 pm, "joel garry" <joel-ga..._at_home.com> wrote:
> 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.

[]
>
> > 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.

> So... where is the actual data?
>
> If the columns are metadata, perhaps you could have one table with
> attributes of the report name and report columns? Did this originally
> come from some kind of report generator? Are there multiple
> definitions within columns?
>
> Maybe if you give an example of what what of these tables look like we
> might have better advice for you.
>
> jg
> --

I agree with joel, this definitely does not require one table per report. It also does not need megacolumn tables either. I've seen and worked on what I think are similar systems. Take a step back and reconsider your design.

Ed Received on Mon Nov 13 2006 - 13:37:45 CST