SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


I have a doubt about my db design


I have a doubt about my db design

Author
Message
simflex-897410
simflex-897410
Right there with Babe
Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)

Group: General Forum Members
Points: 755 Visits: 767
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214520 Visits: 41979
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214520 Visits: 41979
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
simflex-897410
simflex-897410
Right there with Babe
Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)Right there with Babe (755 reputation)

Group: General Forum Members
Points: 755 Visits: 767
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
klini
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 243
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search