I have a doubt about my db design

  • 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.

  • 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)

  • 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)

  • 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.

  • 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

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

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