Bloated Program

  • Thread starter Thread starter Randy
  • Start date Start date
R

Randy

I have a program that basically has about 8 fields that are just client
identification information, but have about 200 Yes/No fields. This
program to me is fairly bloated compared to others I have created. I
have compressed the program.

Is there anything I can do to the Yes/No fields that will reduce the
size of the program?
 
Yes, normalize your database.

If you have 200 attributes that can be applied to a client, then you should
create a related one-to-many table or a many-to-many relationship and enter
only the attributes that apply to each client. In the following scenario,
you'd enter the 200 possible attributes in the "TblPossibleEntries" table.
Then you'd use them in the TblAttributes to link each client to the
attributes that fit them.


TblMain
ClientNumber
ClientName
ClientAddress1
..
..
..


TblAttributes
ClientNumber
CleintAttribute


TblPossibleEntries
AvailableAttributes
 
Rick

Are you saying I would have to set up a table for each of the 200 check
boxes? Any or all of the check boxes could apply to a client.
 
You should have a table that stores the Client ID and the Category ID. If
categories 2, 4 and 9 apply to client 123, you'd have 3 rows in that table:

ClientId CategoryId
123 2
123 4
123 9
 
Randy said:
Rick

Are you saying I would have to set up a table for each of the 200 check
boxes? Any or all of the check boxes could apply to a client.
Click to expand...

No, I think he's saying that you can group them. Are they totally
random, or is a "Yes" in box 50 often associated with "Yes" in box 69?
If they're related, they could share a record in a Table, and other
unrelated boxes could be in another Table. The record would contain, in
addition to some of the check boxes, a key linking it to the client's
information in the main Table.

Doing all this will actually *increase* the size of the database unless
you can arrange it so that some of these Tables will frequently contain
records that are entirely blank and can thus be omitted. Otherwise, I
think you might just as well keep everything in one record; Access
doesn't care unless the number of fields reaches 255.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
Douglas said:
You should have a table that stores the Client ID and the Category ID. If
categories 2, 4 and 9 apply to client 123, you'd have 3 rows in that table:

ClientId CategoryId
123 2
123 4
123 9
Click to expand...

This is an especially good solution if you typically have only a few
boxes checked (= [CategoryId] values) for each of your clients. If most
of the boxes are usually checked, however, you'll wind up with lots of
records in this Table.

You could define your [CategoryId] values, for boxes that are usually
checked, to be true if and only if the box is NOT checked. Then you'd
store records only for the less frequent cases and keep the size of your
Table down that way.

Or you might use a combination -- check boxes in the main record for
some properties, [CategoryId] values in linked records in a separate
Table for other properties.

In any case, I suggest that you CLEARLY identify in comments or
elsewhere, for anyone else who later has to maintain the database, or
for yourself six months from now, EXACTLY what each checkbox or category
value means. Otherwise, you'll have endless trouble and be sorry you
ever embarked on this project.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
Back
Top