One table or a couple?

"David W. Fenton" wrote:

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.

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/

"Tony Toews [MVP]" wrote in
:

"David W. Fenton" wrote:

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.

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?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/