How do I complete this table design? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How do I complete this table design?

Author

Topic

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2014-01-10 : 11:07:56

Greetings Experts,

I have 3 tables called Customer, Prescriptions, and Refills

Customer table contains customer information like firstname, lastname, phone, email, PharmacyName and PharmacyPhone (where prescriptions are being transferred from), etc. CustId is the identity seed (primary key).

Prescriptions table contains all drug prescriptions either transferred from another pharmacy or just prescribed anew.

Finally, Refills table contains a drug refills.

Anytime a customer refills a prescription drug, information about that refill is stored in the Refills table.

Currently, I have succeed designing and coding the INSERT statement for Customer and Prescriptions tables.

For instanace, currently, a new customer registers and provides all pertinent personal information.

Then once that customer's information is stored on the Customer table, we grab the CustId and insert that along with prescriptionId and Prescription name into the Prescriptions table.

This works fine.

What I am not certain about is how to go about coding the INSERT statement into the Refills table.

For instance, the Refills table has following fields.

RefillId, PK, identity seed,
PrescriptionId, FK (from Prescriptions table),
CustId, FK (from Customer table),
PrescriptionName nvarchar(50),

How do I write the INSERT statement so PrescriptionID (based on prescription customer is trying to refill), CustID (from customer is is attempting the refill) and of course, RefillID (auto generated) and PrescriptionsName?

Your kind assistance is greatly appreciated.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-01-10 : 17:14:16

Do "Refills" cover the initial Prescription, too? If not, how do you track the initial prescription?
How does a Customer get related to their prescription?
Why do you need Refills.PrescriptionName when you already have the PrescriptionID which, I assume, would yield Prescriptions.PrescriptionName?
Actual schema definitions (CREATE TABLE), including foreign key definitions, would help to illuminate your situation and make it ~~easier~~ possible to help you.

=================================================
A man is not old until regrets take the place of dreams. - John Barrymore

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2014-01-10 : 20:17:37

Bustaz, thank you for your response.

Actually, I have made a few more changes to the schema since I posted this thread.

Now, I have Customer table
Custid PK
rest of customer info

Pharmacy table
ParmacyId PK
PharmacyName

Prescription table
PrescriptionId pk
CustId FK to Customer table
PrescriptionName

Refill table
RefillId PK
ParmacyId FK to Pharmacy table
CustId FK to Customer table
RefillNumber

I think this looks like it might work.

Now to answer your questions below:

Do "Refills" cover the initial Prescription, too? If not, how do you track the initial prescription?
How does a Customer get related to their prescription?

No, Refills must happen *after* customer has either transferred prescription from another pharmacy or ordered initial prescriptions.

Well, I thought that a customer can have one or more prescriptions.
So, if that's true, then then Customer is related to Prescriptions by CustId.

Please let me know what loopholes to cover.

Thank you very much.

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-01-13 : 07:12:50

simflex, your rework looks a lot better. I had some of the same questions that Bustaz Kool had but could not think of a good way to ask.
The only other thing could be to have a Medicine table that would list the PrescriptionName and relate to the Prescription table. This would normalize more but is not strictly needed.

djj

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-13 : 07:45:24

quote:
Originally posted by simflex

Bustaz, thank you for your response.

Actually, I have made a few more changes to the schema since I posted this thread.

Now, I have Customer table
Custid PK
rest of customer info

Pharmacy table
ParmacyId PK
PharmacyName

Prescription table
PrescriptionId pk
CustId FK to Customer table
PrescriptionName

Refill table
RefillId PK
ParmacyId FK to Pharmacy table
CustId FK to Customer table
RefillNumber

I think this looks like it might work.

Now to answer your questions below:

Do "Refills" cover the initial Prescription, too? If not, how do you track the initial prescription?
How does a Customer get related to their prescription?

No, Refills must happen *after* customer has either transferred prescription from another pharmacy or ordered initial prescriptions.

Well, I thought that a customer can have one or more prescriptions.
So, if that's true, then then Customer is related to Prescriptions by CustId.

Please let me know what loopholes to cover.

Thank you very much.

Why should refills be linked to customers? I think it should rather be linked to prescriptions than to customers as its prescriptions that are getting filled. And indirectly it gets linked to customer as well through custid fk column in prescription table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-01-13 : 09:37:28

quote:
Originally posted by simflex
Do "Refills" cover the initial Prescription, too? If not, how do you track the initial prescription?
How does a Customer get related to their prescription?

No, Refills must happen *after* customer has either transferred prescription from another pharmacy or ordered initial prescriptions.
Please let me know what loopholes to cover.

Initial prescription needs to be captured somewhere, otherwise you won't know the Pharmacy at which it was issued. I think it makes sense to change the "Refill" table to a fill_or_refill (not necessarily with that specific name) table.

As Bustaz noted, the fill and/or refill would normally point back to the PrescriptionId, and not the CustomerId directly. Of course you can de-normalize and add the CustomerId to the fill/refill table also if you really need it there.

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2014-01-13 : 09:51:38

Fantastic feebacks from all of you.

Thank you.

So, assuming that the following is the new design.

Pharmacy table
ParmacyId PK
PharmacyName

Prescription table
PrescriptionId pk
CustId FK to Customer table
PharmacyId FK to Pharmacy table
PrescriptionNumber

Refill table
RefillId PK
PrescriptionId FK to Prescription table
PrescriptionNumber - same name as PrescriptionNumber on Prescription table.

Given this, I am now about confused as to how to insert records into Refills table.

Scott, one thing I wanted to clarify is that Initial Fills are done inside a store since they would not allow a patient or client to call in initial Prescription.

For instance, they tell me that if a patient is prescribed some drugs, initially, that patient must come in with doctor's prescription. After which, refills can be done online via phone or website.

So, Refills table will be used strictly for refills only.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-13 : 14:19:55

you dont need PrescriptionNumber again in refills as it already has reference field PrescriptionId to link to corresponding prescription record to get all the prescription attributes.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources