Need advice on DB design (Access)

Need advice on DB design (Access)

Post by Wayne Floy » Wed, 27 Nov 1996 04:00:00



(Stop me before I re-design again)

Need the most efficient (fastest loading and general access) design for
my VB4 Pro/Jet 2.5 project. I now have one table with a large number of
fields. The data is being displayed and modified in 3+ (several on tabs)
side-by-side grids. Some fields are calculated, but not displayed.

I could break it into several tables, to match the grids, if that would
help... but I must be able to sort the whole affair. Could I join
separate tables, sort, then pull new recordsets for the grids (sorting
speed is less important)?

What are my options?
Thanks much, Wayne Floyd

 
 
 

Need advice on DB design (Access)

Post by Chris Ashto » Thu, 28 Nov 1996 04:00:00




Quote:> (Stop me before I re-design again)

> Need the most efficient (fastest loading and general access) design for
> my VB4 Pro/Jet 2.5 project. I now have one table with a large number of
> fields. The data is being displayed and modified in 3+ (several on tabs)
> side-by-side grids. Some fields are calculated, but not displayed.

How many ways are there to skin a cat? [answers on a postcard to...:-) ]

I'm a little confused as to why you have several grids side-by-side - if
this is because you need to keep some columns in place while others are
scrolled, get Apex's True DB Grid (Pro version) which allows you to define
non-scrolling regions.

For each grid have a data control defined as a TableType Recordset, with
its own set of fields, then in the reposition events use the SEEK method on
the index of the other data-controls to synchronise them (you'll have to be
careful with data validation though - this is a favourite route to the
'Action Cancelled by Associated Object' error - THE most useless error
message after 'Keyboard Error, press F1 to Continue').

I don't suppose this method is any more efficient than splitting the table
into several as you'd still need to synchronise them, however you would
then have to cope with cascade deletes and additions (and updates
probably).

I would have a good long talk to the Users of this application to see
whether they can change the way they work, and if not, get 'em involved
with the design more closely, after a while they'll probably be more
malleable when it comes to your suggestions on making your life easier (not
that you'd couch it in those terms of course!).

--
Chris Ashton
Applications Design Analyst

iQ Computing Limited, Uk

 
 
 

Need advice on DB design (Access)

Post by Joshua Ster » Thu, 28 Nov 1996 04:00:00


Is the table 3rd normal form?

Why do you want to sort the tables, as opposed to the results of queries?

Are there over 100k rows?

J.



Quote:> I could break it into several tables, to match the grids, if that would
> help... but I must be able to sort the whole affair. Could I join
> separate tables, sort, then pull new recordsets for the grids (sorting
> speed is less important)?

 
 
 

Need advice on DB design (Access)

Post by Wayne Floy » Sat, 30 Nov 1996 04:00:00


Wayne Floyd:

Quote:>> (Stop me before I re-design again)
>> Need the most efficient (fastest loading and general access) design for
>> my VB4 Pro/Jet 2.5 project. I now have one table with a large number of
>> fields. The data is being displayed and modified in 3+ (several on tabs)
>> side-by-side grids. Some fields are calculated, but not displayed.
>> I could break it into several tables, to match the grids, if that would
>> help... but I must be able to sort the whole affair.

Chris Ashton:

Quote:>I'm a little confused as to why you have several grids side-by-side - if
>this is because you need to keep some columns in place while others are
>scrolled, get Apex's True DB Grid (Pro version) which allows you to define
>non-scrolling regions.

Wayne Floyd:
Chris, the fields in this large table lend themselves to
sub-categorization, thus display in separate grids. As these would be
too many to display on screen, they also fit nicely onto tabs. Really
makes a slick interface. I'm using Spread 2.5, which seemed faster and
more easily programmed than the various releases of the Apex standard
products. (Never had the chance to try their pro version.)

Quote:>For each grid have a data control defined as a TableType Recordset, with
>its own set of fields, then in the reposition events use the SEEK method on
>the index of the other data-controls to synchronise them.

So, selecting fields (say, 14) from my large table for a grid would be
more efficient than specifying the entire table as its recordset? Is
there no overhead for extracting specific fields? How about selecting
100 fields (spreadsheet type cells) for another grid... is there a handy
SQL command to get a range of fields? All I've noted were commands to
get several fields by name. Or, if I'm going to keep the table in one
piece, maybe I should just assign clones of the table's recordset to
each grid?

Thanks, Wayne Floyd

 
 
 

Need advice on DB design (Access)

Post by Wayne Floy » Sat, 30 Nov 1996 04:00:00


Wayne Floyd:

Quote:>> Need the most efficient (fastest loading and general access) design for
>> my VB4 Pro/Jet 2.5 project. I now have one table with a large number of
>> fields. The data is being displayed and modified in 3+ (several on tabs)
>> side-by-side grids. Some fields are calculated, but not displayed.
>> I could break it into several tables, to match the grids, if that would
>> help... but I must be able to sort the whole affair. Could I join
>> separate tables, sort, then pull new recordsets for the grids (sorting
>> speed is less important)?

Joshua Stern:

Quote:>Is the table 3rd normal form?

You lost me...

Quote:>Why do you want to sort the tables, as opposed to the results of queries?

Ok, I could have been clearer. My question: Can you take several
recordsets (drawn from several tables), join them, sort the joined
recordset, AND FROM THAT, take new recordsets for display in several
bound grids?

And, If that is possible, would there be any net gain in using these
several smaller tables, as opposed to using one LARGE table, which is
much more easily sorted.

Thanks, Wayne Floyd

 
 
 

1. DB Design Advice Needed

Hello all...

I've got a table of  master table of names/address and a detail table of
orders.  I need to produce statistical counts, such as:

  Last 6 Months orders by SCF (1st three digits of ZIP code)
  Last 12 Months order by SCF
     etc.

  Last 6 Months $10-99 orders by SCF
  Last 12 Months $10-99 orders by SCF
    etc.

Plus a whole lot more of the same thing.  In every case, I just need a
record count for everything that meets the criteria.  Sometimes the criteria
will be in the master table, sometimes in the detail, and sometimes in a
combination of the two.

What is the most efficient way to generate this mess?  Is this a single
Oracle Reports?  Or do I need to run lots of queries first to generate the
individual statistics and store them in tables, then Report them?

Also welcome are places on the web to read up on this type of design....

TIA

Eric Raskin

2. connect sql server

3. DB design advice needed

4. Getting data from a table that has been updated after a certain date?

5. Need Db design advice

6. SQL 7.0, XML, XML DOM, and CGI project

7. Need DB design advice

8. SCSII Backup for Pick (OA) on IBM RT

9. Need design help with Access Db

10. Relational DB Design Woes (Any Advice?)

11. Advice on big kid db design

12. db design advice

13. Relational DB Design Woes (Any Advice?)