If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Relationships between tables
I need help! I have a DB with 5 tables (Program, Director,Alt contact,Private Practice, hospital,and Paytype) These are the relationships between them.
There can be one program associated with one director, and program names can be very similar. There can only be on hospital associated with one program. There can be many Private practices associated to one director. and there can be many alt contacts associated with one director. I need to pull reports that show, what director is accoicated with which program,, and all of that directors private practice numbers, and the hospital that his/her program is associated with. Can someone please give me some insite as to how to set up the relationships between each of these fields so that when I do data entry, i will be able to see the correct infomation. For instance. I have a program i need to connect to a direector, this infomation is already in,, but i get a new private practice number for that director, i need to be able to associate an existing director to a new private practice,, and then pull the above mentioned report. Help please,, and if i havent made myself clear,, or you need further information to answer my question please email me |
#2
|
|||
|
|||
Relationships between tables
Mel:
Perhaps you can get by with only 3 tables (I am ignoring the PayType table you mentioned but did not discuss) If the relationship between program and director and bewteen hospital and program is truely one-to-one, then don't these tables really describe the same entity? If so, then you can combine the fields from all three into a single table. Typically you only create a one-to-one relationship among tables when 1) there are security issues (i.e, some fields are sensitive) or 2) for performance improvement (to move large, infrequently queried fields off by themselves in a separate table). Thus, you would have your "director" table (which now contains "hospital" and "program" attributes) and two subsidiary or child tables; "Alt contact" and "Private Practice." These are referred to as child tables because each is the "many" side of a one-to-many relationship with the "Director" table. Director is the parent because it is the "one" side of the relationship and a parent can have many children. Both of the child tables will have a field called "directorid" or something similary that relates (or ties) them to their parent. So, to get the all ofthe information you are seeking for a particular director, you might write the following query: SELECT * FROM director d LEFT JOIN altcontact a ON d.directorid=a.directorid LEFT JOIN privatepractice ON d.directorid=p.directorid WHERE directorid = 10 This will return all of the private practice and al contact records for director #10. The LEFT JOINs ensure you get director #10's director information even if he has no private practice or alt contact records. Otherwise you won't get anything back if there are no private practice or alt contact records for director #10. Dave "Mel" wrote in message ... I need help! I have a DB with 5 tables (Program, Director,Alt contact,Private Practice, hospital,and Paytype) These are the relationships between them. There can be one program associated with one director, and program names can be very similar. There can only be on hospital associated with one program. There can be many Private practices associated to one director. and there can be many alt contacts associated with one director. I need to pull reports that show, what director is accoicated with which program,, and all of that directors private practice numbers, and the hospital that his/her program is associated with. Can someone please give me some insite as to how to set up the relationships between each of these fields so that when I do data entry, i will be able to see the correct infomation. For instance. I have a program i need to connect to a direector, this infomation is already in,, but i get a new private practice number for that director, i need to be able to associate an existing director to a new private practice,, and then pull the above mentioned report. Help please,, and if i havent made myself clear,, or you need further information to answer my question please email me |
#3
|
|||
|
|||
Relationships between tables
Dave,
Thank you this is very helpful,, I will probably set it up this way. I have one other question for you then,, in yous SQL you pulled a report on one director,, is there any way to pull all the same information on all directors that meet a specific criteria,, say all in a specific state and have all of their PP or Alt contact come up? Thanks again "Dave" wrote: Mel: Perhaps you can get by with only 3 tables (I am ignoring the PayType table you mentioned but did not discuss) If the relationship between program and director and bewteen hospital and program is truely one-to-one, then don't these tables really describe the same entity? If so, then you can combine the fields from all three into a single table. Typically you only create a one-to-one relationship among tables when 1) there are security issues (i.e, some fields are sensitive) or 2) for performance improvement (to move large, infrequently queried fields off by themselves in a separate table). Thus, you would have your "director" table (which now contains "hospital" and "program" attributes) and two subsidiary or child tables; "Alt contact" and "Private Practice." These are referred to as child tables because each is the "many" side of a one-to-many relationship with the "Director" table. Director is the parent because it is the "one" side of the relationship and a parent can have many children. Both of the child tables will have a field called "directorid" or something similary that relates (or ties) them to their parent. So, to get the all ofthe information you are seeking for a particular director, you might write the following query: SELECT * FROM director d LEFT JOIN altcontact a ON d.directorid=a.directorid LEFT JOIN privatepractice ON d.directorid=p.directorid WHERE directorid = 10 This will return all of the private practice and al contact records for director #10. The LEFT JOINs ensure you get director #10's director information even if he has no private practice or alt contact records. Otherwise you won't get anything back if there are no private practice or alt contact records for director #10. Dave "Mel" wrote in message ... I need help! I have a DB with 5 tables (Program, Director,Alt contact,Private Practice, hospital,and Paytype) These are the relationships between them. There can be one program associated with one director, and program names can be very similar. There can only be on hospital associated with one program. There can be many Private practices associated to one director. and there can be many alt contacts associated with one director. I need to pull reports that show, what director is accoicated with which program,, and all of that directors private practice numbers, and the hospital that his/her program is associated with. Can someone please give me some insite as to how to set up the relationships between each of these fields so that when I do data entry, i will be able to see the correct infomation. For instance. I have a program i need to connect to a direector, this infomation is already in,, but i get a new private practice number for that director, i need to be able to associate an existing director to a new private practice,, and then pull the above mentioned report. Help please,, and if i havent made myself clear,, or you need further information to answer my question please email me |
Thread Tools | |
Display Modes | |
|
|