Database Answers Pebble Beach

Home Ask a Question Careers Contact us Data Models First Timers Search Site Map
Sperm Bank

The Draft Business Rules have been defined and are listed below.
The Area being Modelled is :-
A Sperm Bank.

The application is a sperm bank.

It’s a combination of a CRM system, a logistics system, and a billing system.
There the logistics are unit identified (i.e. not that there are widgets in location X, 
but that some of  the widgets of Mr Smith are inlocation X.

There are a couple of major areas, firstly the patient details, and secondly the “sample” details… 
  • Patient details – obvious things – title,name, address, phone numbers, email. There is also a need for a flag that indicates whether the mailings are to go to the patient or their Next of Kin (In cases of freezes pre chemo, the patient may be “terminally ill” and it’s politically incorrect to send them bills etc..)
  • Each patient will have a unique customer number, as will each Next of Kin (as these are usually spouses). There is also a “nature of relationship” field between patient and NOK, and each “Couple have an ART Number (Assisted Reproductive Therapy) for the “pair” (arguably still patient plus partner, although NOK may not necessarily be Procreation partner.
  • Firstly, each patient has two “related”tables. Their next of kin, and their referring doctor. For each of those,usual details, name address phone number etc.
  • Elsewhere a transaction log, that reflects the historical changes of any fields in these tables - i.e. address changed from blah to mumble for next of kin XYZ….
  • Both of these are effectively a one to many, in theory (each patient can only have one Next of Kin, and one doctor) but arguably each NOK can be NOK for multiple patients, and the same for doctors, but in practicality that may not be worth worrying about, and just live with duplicate records in the nok and doctor tables.
  • OK, now for the fun part...
  • For each patient, there are multiple “freezes” a freeze is when they come in and donate sperm.
  • The Sperm is sampled, tested for motility and count (how active, and how many wrigglers there are), and then broken down into a number of “ampoules”or “straws”.
  • Each of these will relate back to the date patient,count, and motility data for that patient.
  • At that point a decision is made say, that it is possible to create 10 ampoules out of that “freeze”.
  • There can be multiple freezes – Someone can come back a week, or a month,or a year later and donate some more.
  • There isn’t really any FIFO on this aspect, the geneticist looks to things like recorded motility as the best indicators of what samples they may wish to use down the track…
  • While once an ampoule has been used, there is still a need to track it’s details (i.e. don’t just remove it, flag it as having been consumed, when where, why and by who etc (and adjust total stock on hand for that client).
  • Each of those ampoules are then assigned to locations. Locations are a) Laboratories, b) Tanks and c) and Buckets, (of Liquid Nitrogen).
  • A lab can have anywhere from 1 to 30 “tanks”.
  • Tanks are simply numbered, but there is no guarantee that the number range is contiguous as say tank 14 may be “decommissioned” and replaced with tank22.
  • Tank Numbers are not unique, either. There can be a tank #3 in Lab ABC, and a tank#3 in Lab XYZ.
  • Each Tank there can be up to 10 “buckets” Again, buckets aren’t unique or guaranteed continguous, and are again just identified by a number. I think 20 straws/ampoules per bucket (they are individually labeled as well).
  • There is, of course, the need to select and consume said “ampoules” at varying points in time, and track that they have been “used”, when, and for what purpose (ivf, vs icsi, and other “procedures”), and of course “adjust” the total stock on hand for that client, as it will be the ultimate billing system indicator. The need exists to be able to move “ampoules from Tank to tank, and Lab to Lab (Different destinations)”
  • There is also the billing perspective of this. There is a charge for initial capture and storage (12 months). There is then a billing need to bill from the (nominal 12 month) anniversary of thatinitial “freeze”. Although it would be good to have the ability to have 3/6/9/12 month anniversaries.
  • Multiple freezes come under the same bill - i.e. store as much as you like for the same fixed price.
  • The first bill include 12 months forward i.e. say Jan 05 to Jan 06, but then the need exists to bill in arrears. i.e. at Jan 07, if there has been “stock on hand” forany part of Jan06 through Jan07, create a single storage bill.
  • The bill decision is purely non-zero stock on hand (aggregated for that client throughall buckets, tanks, and buildings)
  • Down the track there are issues around different types of donors, and different purposes for donors.
  • There are, for example, known donors (family friends) etc, saving donors (people undergoing chemo etc, or inhigh risk situations who may wish to “protect” their ability to procreate (by proxy), and then of course anonymous donors, where access to any patient (and NOK) details other than their “ART” number and customer number need to be hidden…
  • In the situation of known donors, and anonymous donors there is also a gating factor which is that samples are flagged as being in quarantine, until a number of blood tests are complete, to ensure no disease transmittal.
  • In the situation of anonymous donors, it’s also necessary to track (eye color, hair colour, height, ethnicity etc) of donor (as this is effectively a “shopping list”). Best regards Peter Brisbane,Australia.
  • Home Ask a Question Careers Contact us Data Models First Timers Search Site Map