The Biggest Design Problem - HELPPPPP!

  • Thread starter Thread starter laskowv
  • Start date Start date
L

laskowv

I need help with this design issue. My goal is to try to have the database
size as minimal as possible. This is a simple example for what I need. I
need to have a way to enter 10 officers positions for a Company, its
Divisions and its Stores.

There will be 178 terms of office (1921-2099). I will need 10 officer
positions posted to each term for the Company, each of the 52 Divisions, and
then the multiple stores under each Division. Depending on the design, the
Company could either produce 1,780 records or 178 records.

Example A: the 1,780 records, (10 officers x 178 terms), design that table
would look like:
CompanyOfficers (table)
COID (PK)
TermID (FK)
OfficerID (FK)
EmployeeID (FK)

Example B: the 178 records, (10 officers all in one record), the table would
look something like:
CompanyOfficers (table)
COID (PK)
TermID (FK)
Officer1ID (FK) (which would really be the EmployeeID)
Officer2ID (FK) (which would really be the EmployeeID)
Officer3ID (FK) (which would really be the EmployeeID)
Officer3ID (FK) (which would really be the EmployeeID)
Officer4ID (FK) (which would really be the EmployeeID)
Officer5ID (FK) (which would really be the EmployeeID)
Officer6ID (FK) (which would really be the EmployeeID)
Officer7ID (FK) (which would really be the EmployeeID)
Officer8ID (FK) (which would really be the EmployeeID)
Officer9ID (FK) (which would really be the EmployeeID)
Officer10ID (FK) (which would really be the EmployeeID)

The Example B works fine in the screen maintenance and entry. The problem
I'm having is creating subform on the Employee form that lists every "office"
which that employee is held. The "JOINS" are not working out.

Example A is the most correct design, but the problem exists when creating
these for the 52 Divisions and hundreds of Stores in each Division. The
Divisions alone under Example A would generate 92,560 records (52 divisions x
10 officers x 178 terms).

There are currently 467 Stores, hence Example A would now generate 831,260
records (467 x 10 officers x 178 terms); and Example B would generate 83,126
records (467 x 10 officers all in one table)

How would any of you resolve this "design issue"? I really welcome your
thoughts. Obviously, Example B would be preferred, but I don't know how to
make the multiple "self-joins" -- I think that's what they are called.

Please, I call upon all of the ACCESS EXPERTS to help.

Thank you for your time.
 
Your goal should not be to have the database size as minimal as possible.
Rather it should be to have the database work as well as possible. Don't
worry about the number of records. Rather worry about that report which you
need actually producing the correct results in a timely manner plus an eye
towards flexibility if someone adds another officer or two.

Don't even think about doing it the Table B way. You say 10 officers and
have 10 officers across in the table (like a spreadsheet). What happens to
all your forms, reports, and queries when someone says there are now 11
officers? Don't say it won't happen.

Can any of these officers be at more than one store or division? Can an
officer ber in more than one year/Term? In that case you will also need some
joining or linking tables to break up the Many-to-Many relationships.

Why do you need both the Employee ID and Officer ID in the table? Seems
redundent to me.

The way I see it you need at least the following tables. If there are any
M-M relationships, you will also need some linking or bridging tables.

Officers Table
Term Table
Division Table
Store Table.
 
First, I think my concern over database size is just. Using Example A for
the Stores will eventually generate over 800,000 records in one table. I
already have 100,000 in just the Employee table. This gets even more
complicated with "committees"; which there are about 35 positions which will
eventually generate almost 1.5 million records. I will design the committees
the same way the officers are designed; but first I have to get through the
officers issue. I am really worried about performance issues and making the
database too large that will cause it to error.

The OfficerID is that actual position (President, Treasurer, Vice President,
etc...); and yes, I already have a Term table. The officers can hold both
store and division officers positions within the same term.

I want to have the ALL of the officer positions show on the form and then a
combo box for the administrator to just select the EmployeeID holding that
office. This way, a "blank" position is very visible on the screen. I guess
a "continous form" would the way to go here?

Thanks.
 
First, I think my concern over database size is just. Using Example A for
the Stores will eventually generate over 800,000 records in one table. I
already have 100,000 in just the Employee table. This gets even more
complicated with "committees"; which there are about 35 positions which will
eventually generate almost 1.5 million records. I will design the committees
the same way the officers are designed; but first I have to get through the
officers issue. I am really worried about performance issues and making the
database too large that will cause it to error.

The OfficerID is that actual position (President, Treasurer, Vice President,
etc...); and yes, I already have a Term table. The officers can hold both
store and division officers positions within the same term.

I want to have the ALL of the officer positions show on the form and then a
combo box for the administrator to just select the EmployeeID holding that
office. This way, a "blank" position is very visible on the screen. I guess
a "continous form" would the way to go here?

Thanks.
 
A properly normalized database will be smaller than one badly normalized. For
example if you have repeating data in a table, the database will be bigger
than if the repeating data was split off into another table. An example would
be putting in the full name and address of the buyer for each order instead
of just a link to a table where the name and address is entered once.

If your database is well normalized and you have millions of records, it's
very possible that Access is not the right tool for the job. You may need to
upsize to something like SQL Server to be able to handle the amount of data
and get reports to run in an acceptably fast manner.
 
Back
Top