Break Large Table into Smaller, Seperate Tables?

[ryguy7272]

I’m helping a friend with a small DB and it’s coming along well, except for
one part. I am actually fairly close to finishing this thing, but I’m stuck
on something. The DB is for storing and analyzing information for hospital
patients.

Right now I have several types of treatments, all saved in a single Table.
I use several queries, with ComboBoxes, to get the appropriate information
for each treatment. It works fine with several categories of different
treatments in my ‘LookupTable’, but I am wondering if I should break this out
into several separate Tables. The data in this LookupTable is unrelated; I
am just using it as a ‘spreadsheet’ for all my lookups because, with
everything in a single Table, it seems to be easier to maintain. I’m just
worried about potential problems down the road a bit.

Any thoughts, suggestions, recommendations?

Regards,
Ryan---

 

[ryguy7272]

Thanks for the look Bill. I ended up breaking my comprehensive Table into
multiple mini Tables. It took a while, but this may be safer in the long
run, and thus save me a lot of time down the road.

Thanks,
Ryan---

 

[Ken Sheridan]

Ryan:

It may or may not be appropriate to break this Treatments table down into
more than one table. Its important to appreciate that each table represents
an 'entity type', that each column of a table represents an 'attribute' of
the entity type, and that each attribute must be specific to the entity type
(in the jargon its said to be functionally dependent solely on the whole of
the key of the table).

A table might require 'decomposition' into more than one table if there are
columns in it which are not functionally dependent solely on the whole of its
key, e.g. you might have a table of addresses with a City column and a State
column. the State column is not solely functionally dependent on the table's
key, AddressID say, because its also functionally dependent on City (knowing
the city means you know the state). This table would be decomposed into
three tables, Addresses, Cities and States, with a foreign key CityID column
in Addresses and a foreign key State column in Cities, each referncing the
primary keys of Cities and States respectively.

An important thing to understand as regards a hierarchical set of tables
like the above example is that the referencing table, e.g. a table which
represents a company's multiple addresses will reference the table at the
bottom of the hierarchy, the Addresses table in this case, by means of an
AddressID foreign key column, as knowing the address means you know the city
and state via the relationships.

Without knowing more of what data is being stored in relation to treatments
its not possible to be specific of course, but hopefully the above example
will give you an idea of the theoretical context in which the question you
have posed needs to be considered. You might have a TreatmentCategories
table for instance referenced by a Treatments table which in turn is
referenced by a PatientTreatments table, the latter having foreign key
PatientID and TreatmentID columns along with non-key columns such as
TreatmentDate etc.

Ken Sheridan
Stafford, England