Results 1 to 5 of 5

Thread: expert help in database design

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Oct 2005
    Posts
    182

    expert help in database design

    I need help in a database design and would appreciate any help at is of very urgence

    I have two tables called Employees and the other is called Surgeons and keep their details in these tables.

    There is a table called Corresspondance that keeps track of information sent to either the employee or the surgeon.

    Correspondance Table

    CorrID --this is a unique number
    CorrType -- this is a foreign key (For Surgeon it is 1 and for employee it is 2)
    FromID -- this can be either surgeon or employee ID
    ...folloed by details

    Now wat i am faced with the problem is that how can i join from correspondance table to the employee table and surgeon table based on CorrType. By this mean that if CorrType = 1 then join surgeon and if it is 2 then join employee..

    Can anybody help me in this.
    thanx

  2. #2
    Frenzied Member d3gerald's Avatar
    Join Date
    Jan 2006
    Posts
    1,348

    Re: expert help in database design

    y dont you create a single table called Employees that will contain information about all employees including the surgeons then if you have additional inforation about surgeons that is not found in mere employees then, you can have Employee details. in the employee table, you should have a Employee Type Field which will identify if the employee is a surgeon or a normal one. then from the employee table, you can connect it directly to correspondence with ease



    Employee =1=======*=Employee Details

    Employee =1=======*=Correspondence
    On error goto Trap

    Trap:
    in case of emergency, drop the case...

    ****************************************
    If this post has been resolved. Please mark it as "Resolved" by going through the "Thread Tools" above and clicking on the "Mark Thread Resolved " option.
    if a post is helpful to you, Please Rate it by clicking on the Rate link right below the avatar

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Oct 2005
    Posts
    182

    Re: expert help in database design

    the problem is that i have an existing system setup like this and i cannot modify that..i am building a web site on top of it. so any suggesstions.thanx

  4. #4
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,966

    Re: expert help in database design

    Easy.... do a compound join....

    Code:
    SELECT *
    FROM Correspondence C
    LEFT JOIN Surgeons S 
      ON C.FromID  = S.SurgonID
        AND C.CorrType = 1
    LEFT JOIN Employee E
      ON C.FromID = E.EmployeeID
        AND C.CorrType = 2
    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Oct 2005
    Posts
    182

    Re: expert help in database design

    thanx for the tip i'll look into it.

Posting Permissions

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



Click Here to Expand Forum to Full Width