Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

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

From: <rogergorden_at_gmail.com>
Date: Tue, 26 Jun 2007 13:04:18 -0700
Message-ID: <1182888258.449340.154380@u2g2000hsc.googlegroups.com>


On Jun 26, 2:43 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> Comments embedded.
> On Jun 26, 1:16 pm, nirav <shiva..._at_gmail.com> 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!!

Pros:

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

Cons:

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

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US