Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2005
    Posts
    5

    Red face Unanswered: MS SQL 2005 Express DB design question. Please help.

    Hi all! I was given the task by my father who wants me to create an appointment DB for his new law firm and I can't seem to figure out what to do with the Appointment table. I've been stuck on this problem for well a week trying to google the answer to no avail.

    Here's my design so far:
    I have created 3 tables. One for Employees, one for Clients and one for Appointments(going to trials, meeting with the clients etc). The Employees table has the EmployeeID as the primary key and the Clients table has the Telephone as the Primary Key. The Appointment table has two foreign keys and no Primary key with one FK linking to the Clients_Telephone and the other FK linking to the Employee_EmployeeID.

    What I want to accomplish:
    A client calls and wants to set an appointment, the receptionist will take the information and query the appointment table to see if one of the partner named Anderson is busy at, say, 3:00PM to 4:00PM on a certain day. If he is then the information will not be applied and my GUI interface will display an error, if he is available then the information is set into the table.

    I think I'm to use Domain Constraint, but the logic is very fuzzy to me. Thanks!

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    One bit of advice. do not use the phone number as a PK. it can change. As this may be OK for some because all of the FKs can be updated what happens if you have to recover data from a backup into a production database and you can not overwrite the whole database. In this case you need to have primary keys that do not change to match up the records.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Dec 2005
    Posts
    5
    @Thrasymachus:

    Thanks for your advice. I was thinking about using something other than a phone number like a driver's license or SS#, but I think it's unorthodox to ask them to provide such information. Don't you think?

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    This is what I would do. Keep your primary on phone number, but add an identity field to cya.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Any way you can link to an Exchange server, and use their appointment/meeting functonality?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •