Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

I have a doubt about my db design Expand / Collapse
Author
Message
Posted Saturday, January 11, 2014 1:34 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 5, 2014 7:59 AM
Points: 187, Visits: 725
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.
Post #1530055
Posted Sunday, January 12, 2014 11:15 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1530131
Posted Sunday, January 12, 2014 11:28 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1530133
Posted Sunday, January 12, 2014 5:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 5, 2014 7:59 AM
Points: 187, Visits: 725
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.
Post #1530163
Posted Monday, January 13, 2014 2:13 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, November 23, 2014 1:26 PM
Points: 15, Visits: 192
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
Post #1530457
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse