Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: implications of merging 17 tables into 1...

Re: implications of merging 17 tables into 1...

From: <>
Date: Tue, 26 Jun 2007 13:04:18 -0700
Message-ID: <>

On Jun 26, 2:43 pm, "" <> wrote:
> Comments embedded.
> On Jun 26, 1:16 pm, nirav <> wrote:
> > Hi,
> > We have a data model and part of the applications' functionality is
> > there in a group of 17 tables to 20 inter related tables.
> I'm lost, are there 17 tables or 20 tables?
> > Now there is
> > an initiative to redesign this piece entirely and the architect is
> > suggesting just one mega table instead of these 17!
> And?
> > What could be the
> > pros and cons of this approach?
> You know more than we do at this point. HOW is this architect
> proposing to consolidate these tables into one?
> > Does it mean that there will be more self joins for the same table and
> > writing queries for getting required data will be more complex?
> How should anyone know, given that you haven't explained how these
> tables are currently defined nor have you provided a layout for this
> proposed new table?
> > What would be the right approach to analyze such a proposal?
> Provide much more information than you have.
> > With thanks,
> > Nirav
> David Fitzjarrell

Step 1: Fire the guy!!


One easy sql statement to insert data regardless of datatype, etc. Easy for developers who aren't too swift with sql or error handling.


No referential integrity.
No enforcement of datatypes, (DATE datatype as a varchar2(50) anyone?) Self-joining a table 6 to 9 times to build a record (name, addr1, add2, city, state, zip) that could be retrieved with one record from one table.
Since even the simplest query involves self-joins, you've assured yourself of an unscalable DB.
You can't create MVs on tables using Self-joins, (pre-10g) so you can't ever have a hope of speeding up retrieval times. Back-end batch updates, queries become mind numbingly complex. Without referential integrity, you allow all sorts of data to become corrupt, which when discovered is usually "The Databases" fault.

Currently I am in a project to take one of these big mega-tables and break it down into it's 17or whatever number parts. Sure developers might have to work a little harder, but the gains in performance, data integrity, and a data model that actually describes what the business processes are are worth it.

If you're lead tech or CIO is a former developer, you've got a long road as he/she will see the big PRO!!!! I listed and figure the cons are the DBAs concern.

Good Luck!!

Roger Gorden Received on Tue Jun 26 2007 - 15:04:18 CDT

Original text of this message