I am forming a medical database of all the instruments
used in the various operation trays in the emergency
room. I want to create a different sheet for each tray,
but be able to search for a tool, and see all the trays
that tool can be found in. Can someone give me a quick
runthrough on how to do that?
Well, like any relational database, you need to start with an exercise
in "Normalization".
Identify the Entities - real-life things, persons, or events - of
importance to your system. Each type of Entity will have its own
Table.
Then identify the entities' Attributes - pieces of information that
you need to know about the entity. Each Attribute will be a field in
your table.
Finally, identify the Relationships between the entities: one to one
(rare), one to many, or many to many. Many to Many is very common, and
reqires the addition of another table to model the relationship
itself.
Not having much operating-room experience I'm speculating a bit here
but... I'd guess that there is a many to many relationship between
Instruments and Trays: certainly a Tray will contain many instruments,
and I'd assume that some types of instruments would be found on many
trays. I'd see that you might have the following tables:
Instruments
InstrumentID
InstrumentType
Description
Reusable Yes/No
<other info about this instrument as a physical object>
Trays
TrayID
<I'm not sure what a tray's attributes would be>
InstrumentsUsed
InstrumentID << e.g. the ID of a Retractor
TrayID << the ID of a Tray which should include a Retractor
<other info about this instrument/this tray, if any>
A "Sheet" would be an Access Report - don't get table structure design
confused with presentation!
