January 3, 2009 at 1:14 am
Here it is:
Essentially I am unsure of the questions i need to be considering on database complexity versus usability...hell any questions....
I currently have a number of formulae (pharmaceutical) in a spreadsheet that i think would be ideal to use as the basis of a new internal website: However I have run into what appears to be a snag (very early on!)
The information I have whittled down into the following tables:
Patient (obvious schema name; address; etc)
Source (basically where we get our "Excipients" below)
Equipment (eName (varchar); eRecalibrateDate(date); ID)
Excipients (ID; Name(varchar); safetyinfolink; sourceID)
Product (sort of a more detailed table of Excipients)
(ID; ExcipientID; PurchaseDate(date); ExpiryDate(date); SourceID; cost(smallmoney); PercPure(decimal); txtDetail(nvarchar))
Now this is where i become confused:
In the spreadsheet the Excipients are listed according to a ratio & each quantity is calculated by entering either a per dose strength or %strength and the desired quantity required
for example Progesterone (Strength:3.2% entered Quantity:60g entered)
Calculated values in spreadsheet:
str*qty(*PercPure) = 1.92g Progesterone
Qty - qty(progesterone) = 68g Base Cream
Formula might have Equipment ID's listed as requirements for their preparation.
Instructions on the steps (with reference to the "excipients")
It was my intention to have a formula table
and perhaps a cross reference Excipients in formulae table
Can anyone help (if this is enough insight into my dilemma)...just general questions to help me decide what information is important...repeatable.
im stuck on this design step!
January 3, 2009 at 1:52 am
Hello,
A key decision is, is the DB to be used for Transaction Processing or Business Intelligence? (For a BI DB you would probably want to consider Fact and Dimension tables)
Have you determined how the Entities (i.e. the future Tables) all relate to each other e.g. can an Excipient have one or many Sources?
Once you have decided on this, you can define which Attributes (i.e. the future Columns) belong in which Entity.
Sorry for the question, but do you know Data Normalisation?
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
January 3, 2009 at 3:12 am
I'm afraid i don't know normalisation other than the barest fundamentals, which ...if i was forced to answer would be to seperate anything "repeatable" into its own table!?
I think what i was trying to explain & perhaps where i need to head is that there are "general" "groups" of formulae that might all have the same overall "procedure" but the individual components might differ
Guess i dont just want a table of every conceivable variation in strength/additional excipient listed as a distinct formula
but then how to accommodate this in a record of what was actually prepared for the patient?
formula id and x? fields to allow for extra additives that may or may not be used?
the main idea is to produce standardised formula & record such, allowing for minimal patient variance...color/fragrance/flavour
I warn you, I've had a wine & this wasnt clear in my mind BEFORE i started.
A key decision is, is the DB to be used for Transaction Processing or Business Intelligence? (For a BI DB you would probably want to consider Fact and Dimension tables)
Have you determined how the Entities (i.e. the future Tables) all relate to each other e.g. can an Excipient have one or many Sources?
Once you have decided on this, you can define which Attributes (i.e. the future Columns) belong in which Entity.
sources & excipients i have thought of... am excipient would have a "usual" source...where as each product (ordered & physical representation of the Excipient) would have a specific source where it ACTUALLY came from. Therefore via the products table an excipient might have several sources but only one "usual source"
I guess you have nailed my prpblem as i am trying to do 2 things with this database/ program. Store Business Intelligence (formulae) and also record transactions ...records of dispensings to patients of those formulae or variations of them.
Perhaps I should consider one only...but i think the two must be linked as a formula might exist, but can be altered as it is prepared on an individual basis!?
thank you for your insight & interest
Michael
(small town pharmacist & would be web developer)
January 3, 2009 at 6:01 am
Hello Again,
You are right about needing to separate repeatable items into there own Table. It is also important to associate Attributes with the correct Entity.
Got to admit that I don’t completely understand what you want to do. You could consider posting samples of the data, as that might help people to help you.
As an idea for consideration, could a “Formula” entity work as a Link table i.e. each Formula would be multiple records within this Table, each having the same Formula-Identifier, but a different Product-Identifier (pointing to the relevant record in the Products table)?
Personally, I would concentrate on a Transaction design for the DB. If the reporting gets too difficult or inefficient, you can think about a separate BI DB later that is built out of extracts from the Transaction DB.
Hope that helps.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
January 3, 2009 at 10:04 am
Michael Artz (1/3/2009)
I guess you have nailed my prpblem as i am trying to do 2 things with this database/ program. Store Business Intelligence (formulae) and also record transactions ...records of dispensings to patients of those formulae or variations of them.
No, storing formulas is still OLTP. I think that you should stick with OLTP for now, you can add OLAP/BI onto it later if you need to, but it is hard to go the other direction.
The only real trick here is the Formulas, because they are really a kind of meta-data. You might want to list some examples for us.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 3, 2009 at 10:15 am
Michael Artz (1/3/2009)
I warn you, I've had a wine & this wasnt clear in my mind BEFORE i started.
Well, if I've learned anything from the movie Swordfish, it's that heavy alcohol usage actually enhances programming skills.
Oh, and that programmers are cooler than rock stars and that hot women like Halle Berry want to hook up with drunken derelict programmers. I am still trying to figure out how I avoided that my entire career. 😀
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 3, 2009 at 3:44 pm
I think i am beginning to understand why it is i dabble in programming...thanks for the insight RBarry!!
Obviously if Halle Berry were to encounter you I am confident she would be instantly attracted to you.... unfortunately I am equally confident she would see through my shameful attempt at trying in some feeble way to be a "programmer"....(i seem to have the drunken derelict part under control.)
I am thinking that explaining this in writing will probably present the solution to me, but appreciate ALL input.
...perhaps data will better demonstrate the dilemma that has me stuck. metadata / LinkTable all sounds very sexy to me....
Troche:
may contain upto 4 "active" Ingredients
Prog 400mg
Test 200mg
DHEA 100mg
BiEst 0.75mg (all expressed as a perdose)
This is a straight multiplication to get the actual weighable amounts (33 doseforms)
And an additional multiplier as to %pure from the Products table (just realised there will eventually be multiple "products" as new stock arrives, this data (ie current product would be best kept in the Excipients Table??)
Weighable amount of Prog = (400*33)/.98 etc
The remaining Excipients are a calculated from the above Actives:
Silica: 0.01*33
OtherStuff: if total actives >300; 0.1*33
Base: (0.94*33) - 0.8*(Actives + "otherstuff") (0.8 = density)
plus some sort of flavouring
I Guess the important things to record per patient/dispensing is the top strengths as well as (perhaps for quality assurance) total weights, both calculated & actual weighed and the flavour specific to that batch.
Also internal recording of time taken etc might live with the dispensing, perhaps an average kept under the formula? (or are such things best calculated each time they are required?...yes i know that answer.... best calculated.... but then i have to do MORE programming!!!, not just rely on a good old M$ sqladapter straight to a table!)
Instructions can remain fairly broad & so can "live" with the formula.
So...what I am thinking is.......
(need to add a field in Excipients (IsFlavour & IsColour)
Formula table
ID
Instructions (containing html of point form steps)
CatagoryID (veterinary/human/...other sorting/reporting sort of info)
then ...what i understand to be "linktables"
FormulaExcipients
FormulaID
ExcipID
density (thought about using "IsActive"...but i believe this is a "neater" solution)
maxmWeight (some sort of check that the input value is "reasonable")
FormulaEquipment
FormulaID
EquipmentID
relevant settings etc
The last table still has me stumped...that is the recorded "dispensing
Dispensings
ID
FormulaID
patientID
FlavourID
date;timetaken;notes;
now...how to record the excipients used?
is it ok just to have 5 field "pairs" (prodID; proddose; prodweight) & not use all of them depending on how many active ingredients are used?
i appreciate your time helping me
Michael
January 4, 2009 at 1:57 am
Hello,
“is it ok just to have 5 field "pairs" (prodID; proddose; prodweight) & not use all of them depending on how many active ingredients are used?”
It would probably be better and more “normalised” to have these as (up to) five records in a Dispensing-Items Table. Each record would contain the PK (ID) from the Dispensing Table.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
January 4, 2009 at 11:03 am
I don't want to discourage you from learning - far from it - but have you considered getting some professional assistance with this system?
It seems a bit alarming that someone with no experience or qualifications is developing a database for healthcare where accurate results could be critical to patients' well-being and privacy and data protection are legal requirements. Maybe your examples were just intended for learning purposes and not for actual use, in which case I hope you'll forgive me for stating the obvious.
January 4, 2009 at 2:33 pm
David Portas (1/4/2009)
I don't want to discourage you from learning - far from it - but have you considered getting some professional assistance with this system?
I Can assure you you are unlikely to EVER discourage me from learning 🙂
As for privacy/accuracy/data protection this is a project to better store and retrieve the information we currently keep on hard copy. We are a small (ish) pharmacy and the eventual project will live on ONE desktop within our business. It will not replace our hard copy records (such would not be legal), but rather supplement and enhance this side of our business.
January 5, 2009 at 3:41 am
Thankyou for this imput, guess i still can't get "link" tables into my thinking, but this makes perfect sense!
January 5, 2009 at 6:29 am
Keep hacking away at it, you'll arrive at something useful. In the mean time, you might want to pick up a book on database design. The one I like is 25 years old, but still useful for all that, Fleming & Von Halle's "Handbook or Relational Database Design"
It really walks you through the fundamentals and provides tools for getting stuff done.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 6, 2009 at 12:59 pm
Since I haven't been an active forum member, I should mention my experience.
I'm a chemistry Ph.D. who worked in a pharmaceutical company method development R&D lab. I also prepared and validated spreadsheet templates for the calculations arising from method validation. It was a world with no pencils, where the pens only had black ink, and every word I wrote, and calculation I performed by hand was checked and signed-off by a data-checker.
What you are starting to touch on is a LIMS - a Laboratory Information Management System - where the methods and formulas are indeed stored, and the actual measurements are also stored. The reality is that you are wanting to replace your existing system. Our company of 350 employees (at the time) was too small to justify a LIMS, which gives an idea of their cost and complexity.
Yes, you will still need to keep paper copies, but you are wanting to push the calcs into your application. This really raises the bar on the application design.
You might want to consider a hybrid approach, where you use familiar spreadsheets (that you can validate and lock down) for calculations, but linked to a backend database to pull stock information (look up lot numbers, etc), and push your measured values. There are ways to embed spreadsheet functionality in web applications, so eventually you can move to a full web version.
As for design hints, start by tracking what you DID put in, rather than what the formula is. So, you will need (and you will probably want to change the terms to match your terminology):
Excipient - could be combo of supplier and chemical, or you could represent the chemical/mixture and the supplier in their own tables - so Supplier, Mixture, Excipient
Sample or ExcipientItem - could be one for each Lot, or one for each physical bottle, depending how you track. An alternative would be to omit this table, and put the Lot Number into ProductComponent below, but that would not allow you to track received date, finished date, etc related to your stock.
Formulation - top-level description of what you are trying to make - name of a recipe
(Later, to represent the items in the recipe you could add FormulationComponent that would be Excipient + DesiredAmount )
Product - an item that you have actually prepared according to a Formulation, will contain multiple ProductComponents
ProductComponent - one for each measured Sample/ExcipientItem with MeasuredAmount.
Have fun.
Oh, and start investigating change logging, whether done in the application at the persistence layer, or via triggers at the actual database level.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply