One table or a couple?

[accesskastle]

Okay, I have a question about denormalizing. I have an activities table.
One particular activity that may take place is the setting of traps, each of
which has many captures. Another activity is hunting, which may also have
many captures. The problem I'm running into is whether I should have
multiple tables to account for the hierarchy or just combine it. Whether it
is a hunt or a trap, the data on the capture is relatively the same, but it
is necessary that I know (if it is a trapping activity) from which trap the
capture it came. Here's one possible structure, which will require a later
union query if I want to find all captures:

tblActivities
ActivityID PK (Counter)
Activity_Date (Date)
Activity_TypeID (Long)
....

tblTrapChecks
TrapCheckID (Counter)
ActivityID FK (Long, from tblActivities)
TrapID (Long)
Baited (Boolean)
....

tblTrapCaptures
TrapCaptureID (Counter)
TrapCheckID (Long, from tblTrapChecks)
AnimalID (Long)
SexID (Long)
Lbs (Currency)
AgeClassID (Long)
ColorID (Long)
....

tblHuntCaptures
HuntCaptureID (Counter)
AnimalID (Long)
Quantity (Long)
SexID (Long)
.....

Or, I could combine the hunt and trap capture tables and leave some fields
null, for example:

tblAllCaptures
CaptureID (Counter)
ActivityID (FK Long, Null in the case of a trapping capture)
TrapCheckID (FK Long, Null in the case of a hunt capture)
AnimalID
Quantity
SexID
Lbs
....

Can I get some advice on this? One table, two, or something else I might
have overlooked. I could be looking at this all wrong; I am still learning
about normalization. It would make sense to combine them so that I just have
one captures table, but I think that'll void referential integrity. On the
other hand, it might be annoying to have to union the data to bring it all
together.

AK

 

[tina]

look for the data that is common to both tables tblTrapCaptures and
tblHuntCaptures. put that data into a single table, as tblAllCaptures. the
capture-type-specific data would remain in tblTrapCaptures and
tblHuntCaptures, as child tables that each have a one-to-one relationship
with tblAllCaptures.

hth

 

[Tony Toews [MVP]]

look for the data that is common to both tables tblTrapCaptures and
tblHuntCaptures. put that data into a single table, as tblAllCaptures. the
capture-type-specific data would remain in tblTrapCaptures and
tblHuntCaptures, as child tables that each have a one-to-one relationship
with tblAllCaptures.

I disagree. Only once have I ever used a one to one relationship.
And that was because I had almost run out of fields on a table. And
yes it was a fully normalized table.

This is also a pain to setup as far as subforms and such as well.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[tina]

since MVP Tony Toews recommends against my suggested setup, i'd say put all
the captures in one table, leaving empty fields in each record where the
data is not applicable for that capture type. i wouldn't build two separate
capture tables, as doing that wuld mean that you're storing data in table
names ("hunt" captures and "trap" captures).

hth

 

[David W. Fenton]

Only once have I ever used a one to one relationship.
And that was because I had almost run out of fields on a table.
And yes it was a fully normalized table.

This is also a pain to setup as far as subforms and such as well.

I've used 1:1 tables a lot. In one app that tracks psychiatric
cases, the table about the case has a 1:1 side table that holds the
demographics, which are collected only when the case is closed. No
subform is needed -- the table is joined with a left join in the
form's recordsource, and the tab with the demographic fields is not
displayed until the main case record is marked as closed. Works
pretty well.

Another less successful use of 1:1 tables was in an app where I used
3 side tables to sub-type a main record type. Basically, the main
table was a "comments" table, but there were three very specific
classes of comments, and the side tables included the fields
specific to the particular subtypes. This worked well in terms of
modelling the data, but not so well in terms of performance, as in
several cases it required 3 left joins in a single recordsource (the
particular one displayed all the comments in a single continuous
subform, with some information from the subtype tables), and that
was a huge, huge performance drain. But that was a Jet back end and
I always suspected that with a server back end, it would not have
been so slow. The app was never completed (the client still uses it,
but I don't work for them any longer, and they've done no further
development work -- I never got around to proposing a SQL Server
upsize to improve performance, partly because I feared that it would
cause as many bottlenecks as it removed), so I never got to test
that hypothesis.

It did teach me that outer joins are *very* expensive in terms of
performance in a Jet database and so I've avoided them in future
schema designs.

The key problem was the requirement for displaying all the records
in a single form, with information displayed from the subtype
tables. Without that requirement, it wouldn't have been a problem at
all.

 

[Tony Toews [MVP]]

since MVP Tony Toews recommends against my suggested setup,

Well hold on a sec. Just because I disagree doesn't mean I'm right.
David brings up some interesting ideas.

i'd say put all
the captures in one table, leaving empty fields in each record where the
data is not applicable for that capture type. i wouldn't build two separate
capture tables, as doing that wuld mean that you're storing data in table
names ("hunt" captures and "trap" captures).

Although I would agree that this solution would likely mean the least
amount of work and, possibly, avoiding some kind of "interesting"
problem in the future.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[aaron.kempf]

David;

I would reccomend that you become knowledgeable with SQL Server before
you continue to spew random crap about how SQL Server would cause as
many bottlenecks as it removed.

Speaking of bottlenecks-- do you see any TPC world records for Jet
databases?

Now why is it that something with 'more bottlenecks' would handle
100,000 times as many transactions as jet could ever handle?

-Aaron

 

[aaron.kempf]

I actually think that 1:1 tables are probbaly the most under-utilized
structure available to anyone.

Of course-- 1 of the tables has to really be a master-- or else it
gets to be a pain in the butt--
but 1:1 tables are a great way to increase performance

(because you can fit a lot more records in a page of data).

Table Design 1 =
Columns 1,2,3,4,5,6,7,8,9,10,11 (20 records per page)

Column1 = Primary Key
Column2 = Some other Key

Thus, if the accounting department only ever looks at columns 3,4,5
and HR only ever looks at columns 6, 7, 8 and purchasing only ever
looks at columns 9,10,11-- then yes-- all three departments would get
a LOT better performance by organizing three tables

1,2,3,4,5 (40 records per page?)
1,2,6,7,8 (40 records per page?)
1,2,9,10,11 (60 records per page?)


Sure, it might cost marginally more storage-- but then again, you guys
don't give a crap about storage space because you use a database that
handcuffs you with crappy datatypes.

If you guys knew anything about optimizing tables you'd use an
efficient db.

-Aaron

 

[accesskastle]

Thanks all for your comments. Doesn't really sound like consensus, but I'm
going to go ahead with the one table for captures along tina's lines because
it is easier, and because it is the same type of information except for the
foreign key.

AK

 

[David W. Fenton]

David brings up some interesting ideas.

Another app I used 1:1 was a medical study data collection app in
which data on all the kidney transplant patients from a particular
hospital was received from the UNOS national transplant registry.
These were imported into one table. If one of those patients
enrolled in the study, a 1:1 record was created in another table
with all the information needed for the study that was not already
included in the UNOS data (and, yes, it was a non-normalized data
structure and turned out to be a huge problem when 3 years in,
before the app was even in production use, UNOS changed their data
structure!). In a case where you're getting part of your data from
one source and need to maintain that, and you also need to add other
data for some (but not all) of the records in the main table, a 1:1
structure is very useful.

It also had the advantage in that case that an inner join gave you
all the enrolled patients without needing to select on, say,
enrollment date Is Not Null.

That app should have been designed as a proper survey structure, but
it did teach me that when you're importing from one source and
adding data from another, it's a perfectly viable structure. I get
asked all the time to work on Access apps that draw part of their
data from a website, and when the Access app needs to track
information different from the website, it can be a pretty useful
structure.

 

[Tony Toews [MVP]]

Another app I used 1:1 was a medical study data collection app in
which data on all the kidney transplant patients from a particular
hospital was received from the UNOS national transplant registry.
These were imported into one table. If one of those patients
enrolled in the study, a 1:1 record was created in another table
with all the information needed for the study that was not already
included in the UNOS data (and, yes, it was a non-normalized data
structure and turned out to be a huge problem when 3 years in,
before the app was even in production use, UNOS changed their data
structure!). In a case where you're getting part of your data from
one source and need to maintain that, and you also need to add other
data for some (but not all) of the records in the main table, a 1:1
structure is very useful.

In that instance then sure a 1:1 can make sense. Obviously that's
relatively rare.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

In that instance then sure a 1:1 can make sense. Obviously that's
relatively rare.

Well, maybe it's just me, but I've created at least three such
applications (that I can recall off the bat) where there was data
imported from somewhere else that the 1:1 table was useful. And
that's in addition to the two other types of 1:1 I earlier
described. All told, I've used 1:1 tables in about 1/3 of all the
major apps I've ever created.

Oh, yeah, I just thought of another one:

In a replicated app, to simplify complex filtering of the main form,
there's a Boolean flag field that used to be in the main table. This
caused terrible problems because each time the tag field was used
for filtering it caused replication conflicts. So, I moved the tag
field to a separate 1:1 table that is unreplicated.

Are you getting the point, Tony, that I use 1:1 tables quite a lot?
And that there really are quite a lot of justifications for their
use?