I have a doubt about my db design

Question

I have a doubt about my db design

simflex-897410

Hall of Fame

Points: 3881
More actions
January 11, 2014 at 1:34 pm

#302629

Greetings Experts,
I have 4 tables called Customer, Pharmacies, Prescriptions, and Refills
Customer table contains customer information like firstname, lastname, phone and email , etc. CustId is the identity seed (primary key).
Pharmacies table contains
PharmacyId pk identity seed,
PharmacyName,
PharmacyPhone
Prescriptions table contains all drug prescriptions transferred from another pharmacy with:
prescriptionId identity seed PK,
custId FK from Customer table
PharmacyId FK from Pharmacies table
prescriptionNumber varchar(25)
Finally, Refills table contains a drug refills.
RefillId PK identity seed
CustId FK to Customer table
RefillNumber varchar(25) (same as Prescription number from Prescriptions table)
Anytime a customer transfers his or her prescription drug, customer's information is stored in Customer table, telephone number and name of pharmacy is stored in Pharmacy table.
Then latest custid, as well as latest pharmacyId along with prescription numbers are stored in Prescriptions table.
This works fine.
What I am not certain to go about coding is inserting custId into the Refills table without inserting another customer info into the Customer table.
How do I write the INSERT statement so RefillNumber (based on prescription customer is trying to refill), CustID (Customer is attempting the refill) and of course, and RefillID (auto generated) are stored in Refills table?
Your kind assistance is greatly appreciated.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

The problem is in the design of the Refills table. It needs to have the ID of the prescription in there or in a bridge table. For something like this, I'll recommend not using a bridge table.

The refill absolutely must be linked to the prescription which must be linked back to the customer. That's the only way this is ever going to work.

I also would NOT call the RefillNumber that. It should be named the same as the prescription ID column so that no one has to guess or rely on esoteric knowledge to know what it is. You DID have to parenthetically explain to us what it was.

To wit, I also wouldn't include the ID of the customer in the Refills table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

To add to that, each pharmacy can have more than 1 phone. I strongly recommend a separate table for phone numbers by pharmacy ID. I also recommend the same for customer phone numbers, addresses, and emails so that you don't end up with an unmaintainable mess further down the road. You might also want to consider Type 2 SCDs for those ancillary tables, as well.

http://en.wikipedia.org/wiki/Slowly_changing_dimension#Type_2

Further, if you do (and you should) go with the Type 2 SCDs for phone numbers, addresses, and the like, consider using the year of "9999" (which will be converted to 9999-01-01 if the column is a proper DATETIME column) for end dates that haven't yet happened. It WILL make coding for the active row(s) a whole lot easier and SQL Server likes NOT NULL columns a whole lot better than NULLable columns.

To be honest (and I'm not trying to be mean), if this is for a real application, I strongly recommend that you get a qualified database designer on the job. Medical/Pharmacutical is just too important for ad hoc design. And, no, I don't even consider myself to be properly qualified to design such a database and I've been working with SQL Server for 18 years. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 3

Hi Jeff,

Thanks a lot for your reply to my thread. It is much appreciated.

No, I don't have any issues whatsoever with your comments or recommendations even if it is an attack on me which it isn't, I still will focus on the positives and there are lots of it from your replies.

You stated I needed to have PrescriptionId on the Refills table as FK to Prescriptions table.

Does this mean that:

1, I don't need Pharmacies table?

2, If yes, what table should I put pharmacyName and PharmacyPhone?

I am also not certain why Refills table should not have CustId from customer table

Remember that the way I am told this works is that a customer MUST have Prescriptions already on record in other to refill it.

So, how can a customer be identified without his/her ID?

Thanks again.

klini SSC-Addicted Points: 458 More actions · Answer 4

This is terrible of me to butt in. Jeff can correct me...

"Why Refills table should not have CustId from customer table"

Indeed: On the Refill table, put PrescriptionID in and pull out the CustID.

This way when you link Refills to Prescriptions (via PrescriptionID) you have about anything you would ever want to know, including CustID (becaues it is already in Prescriptions).

"what table should I put pharmacyName and PharmacyPhone"?

Study his link and see what design you come up with. There's many ways to refactor that.

Kurt