Big Tables or One-to-One?

[sljack]

Which is a better design? A huge table with many fields (approx. 66 fields)
or to break that table up into smaller tables and establish one-to-one
relationships? If I break it up, how do I create the one-to-one
relationship?

 

[Beetle]

There are always exceptions, but, generally speaking, a table with 66
fields is more than likely not properly designed. As far as what type
of relationships you would use if you separate the data into multiple tables,
that all depends on the data itself. If you can provide some type of
idea as to what your data is, someone may be able offer more specific
advice.

 

[John W. Vinson]

Which is a better design? A huge table with many fields (approx. 66 fields)
or to break that table up into smaller tables and establish one-to-one
relationships? If I break it up, how do I create the one-to-one
relationship?

Almost certainly, the appropriate answer is the Zen "Mu" - "unask the
question".

What real-life entity has 66 distinct, nonrepeating, non-interdependent
attributes? What Entity (real-life person, thing or event) is represented by
this table? What are some of the fields?

I *suspect* that some of these fields are actually concealed one-to-many
relationships, and that you may need multiple tables related one to many,
rather than one to one.

On the other hand I *have* (reluctantly, dragged kicking and screaming) built
a couple of 60-field tables. It may be legitimate.

To directly answer your question: if you won't be exceeding the 2000 bytes per
record limit (a nasty little "gotcha" since Access lets you create a table
with 255 fields each 255 bytes, without complaining until you actually enter
more than 2000 bytes into any one record!), go with a single table. To create
a one to one relationship give each table a Primary Key and use the
relationships window to relate the two tables, primary key to primary key.

 

[Armen Stein]

What real-life entity has 66 distinct, nonrepeating, non-interdependent
attributes? What Entity (real-life person, thing or event) is represented by
this table? What are some of the fields?

I agree with John. We've built many complex databases where no single
table had more than a few dozen fields.

If you can, go with one table.

If you do go with one-to-one tables, your forms, reports and queries
will be more complex too, so think about it carefully before you start
down that path.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[sljack]

The database is going to track legal administrative law cases. I've broken
up the data into the following:

tblCaseinfo (currently contains 20 fields)
tblRegion (a many-to-one relationship with tblCaseinfo because each Region
can have multiple cases).
tblRepresentatives (a many-to-one relationship with tblCaseInfo b/c each
representative may have multiple cases)
tblAction (this is the one-to-one relationship with tblCaseinfo since each
case has a specific action, along with date fields relating to when the
action was taken, appealed, etc.)
tblActnDetails (this is a table that contains information about the type of
penalty imposed, amt of penalty, date imposed, etc. This table is a
many-to-one relationship with Action because for every action, there can be a
multitude of penalties)
tblDiscRec - many-to-one with tblCaseinfo
tblDiscSent - many-to-onewith tblCaseinfo
tblHrgs - one-to-one with tblCaseinfo since each case only has one "trial"
(in administrative law they are called "hearings")
tblHrgTasks - many-to-one relationship with tblHrgs
tblIndex - a many-to-one relationship with tblCaseinfo. It is a 3-field
table that logs events for each case.
tblStatus - a one-to-one relationship with tblCaseinfo. Each case only has
one "status" but I split this off because the tblCaseinfo was becoming too
large with too many fields
tblDisposition - one-to-one relationship with tblCaseinfo. Again, each case
only has one disposition, but I split it b/c of size

Here is my dilemma in the design: The first entry of a new case will be in
the tblCaseinfo table. When the user enters the details, he will at that
time also make entries into the tblAction and tblActnDetails tables b/c each
case starts out with that information. But, the case will not necessarily
have information entered about its Status or Hearing (the other one-to-one
relationships with tblCaseinfo) until a later date. I have found that with
the form I have built that if a user begins to enter a new case and gets
interrupted and does not enter information about the Action, than the form
will not display the new record the user began when it is reopened and the
user ends up entering it again, thus creating a duplicate entry. How do I
fix this?

Here is the query for my form the user uses to enter case information:

SELECT tblCaseInfo.*, tblStatus.ClosedDate, tblRegion.ID, tblRegion.SUBOFC,
tblRegion.PM, tblRegion.PMPH, tblRegion.Ext, tblRepresentatives.*,
tblAction.Actn, tblAction.NoticeDate, tblAction.Imposed, tblAction.Appeal,
tblAction.Program, tblAction.Surveys, tblAction.IDROutcome,
tblAction.TotalImposedAmt
FROM tblRepresentatives RIGHT JOIN (((tblCaseInfo LEFT JOIN tblRegion ON
tblCaseInfo.FieldOfc = tblRegion.ID) LEFT JOIN tblStatus ON
tblCaseInfo.CaseId = tblStatus.CaseId) INNER JOIN tblAction ON
tblCaseInfo.CaseId = tblAction.CaseID) ON tblRepresentatives.ID =
tblCaseInfo.RepID;