Modelling:Big Table vs Small Tables 
Author Message
 Modelling:Big Table vs Small Tables

We have a large app that uses "split" tables for our main file.
If I had it to do over, I would create one large table.  You have
to be careful to insure that for every row in the "main" table
you have a row in the other table(s) if they constitute one "big row".
You always have to allow for the possibility that the 2nd row
didn't get added when it should have, and you have the overhead of
the joins to get it back for maintenance and reporting.  It is also
not trivial to present the joined records as "flat" in maintenance
forms.

Quote:

> I'm building a database for a technical company. My main entity
> consists of many attributes. I 've always learned that you should
> divide this entity into sub entities because you 'll get
> maintenance trouble if you don't. On the other side, putting all
> the attributes in one table simplifies the construction of the
> table and of all the views and forms based on the table.
> Can anybody tell me any arguments for dividing the table and can
> anybody tell me arguments for keeping the table as one. I'm
> interested in arguments drawn from experience, because I already
> know what the books tell you about it.



Sat, 31 Jul 1999 03:00:00 GMT  
 Modelling:Big Table vs Small Tables

Daan,

I am jjust finishing my second complex database design in access - using a
Visual C++ front end. The first was a single database with (primarily) one
huge table. The second one I was able to construct :-) which consisted of
many related smaller tables. I find that the basic rules of normalization
usually break the database into smaller tables. If normalization does not
break the table apart for you, there is little reason to do it
artificially.

The only reason you might want sub tables that I can think of is if:
1) You have a number of indeces on the table
2) You will commonly update groups of attributes at a time
3) There might be less overhead of keeping the indeces updates.

Blake Nelson
--
MS Windows Consultant, President
Information Integrity, Inc.
Stow, MA 01775
(508) 897-0801



Quote:
> I'm building a database for a technical company. My main entity
> consists of many attributes. I 've always learned that you should
> divide this entity into sub entities because you 'll get
> maintenance trouble if you don't. On the other side, putting all
> the attributes in one table simplifies the construction of the
> table and of all the views and forms based on the table.
> Can anybody tell me any arguments for dividing the table and can
> anybody tell me arguments for keeping the table as one. I'm
> interested in arguments drawn from experience, because I already
> know what the books tell you about it.



Sun, 01 Aug 1999 03:00:00 GMT  
 Modelling:Big Table vs Small Tables

The main reasons for splitting up the data is to minimize the
duplication of data,  which directly results in smaller data size.

Also,  normalizing the data allows you to leverage referential
integrity and indexing to your advantage. (i.e. faster querying, easier
reporting, etc.)

As for using a single table,  this is fine if the problem is VERY simple,
and future enhancements won't entail any major changes in the data
structure.

I have to say,  I've been using access since it was first released, and I've
been programming for many more years before that,  and I strongly believe you
should ALWAYS normalize your data.  It forces you to be disciplined in your design,
and more accurately represents the "real world" relations in the data.
And it is usually best to try to represent the data as close to it's actual
usage as you can get.

With a realistic model, it is much easier to add further hierachies later if you
have to. You should also consider the fact that someone else may find themselves
maintaining the database down the road,  and it will be much easier to do if the
databases structure is self documenting. (There's nothingn worse than trying
to make major changes to a poorly designed database.)

Obviously,  this method requires more planning and work up front,  and a greater
degree of discipline.  Discipline is ABSOLUTELY vital in programming,  as is
proper form.  No one likes to declare every variable,  comment their code,
or write the same error trapping routine hundreds of times,  but the upfront
effort is more than worth it when you consider the work and frustration you
WILL encounter down the road if you don't.

I know what the books say,  and I've just repeated a lot of it above.  Then
again,  most of those books were written from other's experience over the
years. The Referential model didn't just pop up out of nowhere,  it was
specifically designed to solve many of the shortcomings of earlier systems.

The basic principal I follow is KISS (Keep It Simple Stupid :) ),  but don't
ever take the easy way out.  It will lead to pain and suffering.

Every problem has many answers,  but tried and proven methods are almost always
a good thing.  There may be better methods,  but I'm too busy working to look for
them,  as I'm sure most everyone else is. :)

At any rate,  I don't want to sound like a tyrant or anything,  but I learned the
hard way, and I'd like to help everyone else avoid it.  At any rate,  good luck,
and happy coding.

Quote:

> I'm building a database for a technical company. My main entity
> consists of many attributes. I 've always learned that you should
> divide this entity into sub entities because you 'll get
> maintenance trouble if you don't. On the other side, putting all
> the attributes in one table simplifies the construction of the
> table and of all the views and forms based on the table.
> Can anybody tell me any arguments for dividing the table and can
> anybody tell me arguments for keeping the table as one. I'm
> interested in arguments drawn from experience, because I already
> know what the books tell you about it.

--
/--------------------------------------------------------------\
Quote:
}  The Unknown Consultants    ::    Access Solutions           {
}  George Kinney              ::                               {


\--------------------------------------------------------------/


Wed, 04 Aug 1999 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Linked Table VS Import Table

2. Linking tables vs. Opening tables directly

3. attached tables vs local tables

4. Linked Table VS Import Table

5. multiple table dynaset vs multiple table recordsets

6. Choice to make: One big table or many small tables?

7. Modelling tool for UML (Unified Modelling Lanaguage)

8. big problem to importaion table with a vba code

9. Big problem for printing table ?

10. Two tables, one form , BIG problems

11. Table big problem...

12. VB+DB2/2+big tables??

 

 
Powered by phpBB® Forum Software