My solution is going to be used to match intraocular lenses (IOLs) with patients. I have a question of how best to structure the tables. Here is the workflow:
We have a large consignment of IOLs from several different mfrs, which contains most common styles and diopter powers, so in most cases, when we get a patient name and lens order, we search our stock and it is here. However, sometimes it isn't and we have to special order it, and then of course we need to order replacement stock.
So - I have a table containing all of our lenses in stock (LENSES), and when one is placed in a patient’s eye, I just change its attribute from “in stock” to “implanted”, and match its key field with a patient’s key field in the patient’s table. This works great for lenses in stock and implanted.
However, I am wondering how to treat these “pre-orders” - where I get a surgeon order for a particular patient coming up for surgery (my main table - APPOINTMENTS) and a particular style and power of lens, but no lens yet. My staff currently creates printouts of patient appointments (which contain the lens order), and then checks our inventory and either notes that we have it or not on the printouts. Very inefficient.
In this pre-surgical stage, we are not identifying a particular lens (each lens has a unique serial number given by the mfr), but only a style and power.
Here is my question: should I create another table, called LENSES_ORDERED, that I use to handle these lens orders that don’t have serial numbers yet, and only exist as orders and requests?
My current relationships and proposed ERDs can be seen in the Lenses ERD screenshot.
Then, when the patient (APPOINTMENTS) actually gets a lens implanted, switch the lens id from LENSES_ORDERED to LENSES? I’m stumped. Thanks for any help!