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