Multiple relationships on tables...good idea? 1

[jeffshex]

Is it a problem to have relationships set like the following:

Table1 has it's own ID PK field (we'll call it T1ID)
Table2 has it's PK field T2ID and the FK of T1ID
That's the normal stuff, but is it wrong to have another table:
Table3 has PK T3ID and two FK T2ID and T3ID

I guess why I ask is because I may want to pull information on table 3 based on the PK field from table 2, however I may just want all that data from table 3 only where it relates to table 1.
Hopefully that makes sense.

It just looks weird in the relationship table with the junction table (table2) along with the relationship straight from table1 to table3.

I have looked for normalization and what not and didn't find anything that talked about this.

Do I leave that relationship out from table 1 to table 3 and just create it as I need it on my queries?
Any insight will be helpful.

 

[fneily]

Table3 has PK T3ID and two FK T2ID and T3ID". Typo? Do you mean T2ID and T1ID? Let's go with that.

So what you're saying is that you have a one-to-many relationship between Table1 and Table2.

Table3 is a junction table. So you're saying there's a many-to-many relationship between Table1 and Table2.

Tables can have only one type of relationship between them.

Since Table3 should, and must, contain all the common fields between Table1 and Table2, I'd get rid of T1ID from Table2. So keep the many-to-many.

Would help to see your table structures, though.

 

[jeffshex]

Let me try to detail it out:

tblGeneral      tblProgram         tblAppointments
----------      ----------         ---------------
PK GeneralID    PK ProgramID       PK AppntID
                FK GeneralID (12M) FK ProgramID(12M)
                                   FK GeneralID(12M)
(12M) = 1 to many

In my head, I thought, well I want to be able to pull appointments where it only relates to the program instance...or I would want to pull appointments based only off of the GeneralID and could care less about the ProgramID.

I'm not sure if I should remove the relationship between the tblGeneral and tblAppointments.
I am hesitant to leave it though because I'm not sure if that would affect any data being pulled.
Hope that clears things up.

 

[fneily]

Yeah, get rid of the GeneralID in tblProgram since you don't care if general matches up with any programs. So you have:

tblGeneral ----> tblAppointments <----- tblPrograms
One to Many to One

 

[jeffshex]

Well, I do want to pull data from the program table though, only where it relates to the General.
The General table only contains demographics.
The Program table contains program specifics for the individual from the General table.
I do however would like to be able to pull from the Appointments table data that is either all for that one person or only appointments for that one person for their particular program instance.

So the tblGeneral -> tblProgram is a one to many
tblGeneral -> tblAppointments is a one to many
Also the tblProgram -> tblAppointments is a one to many

I looked at one of your previous posts and it sounded like this may be common. I just want to get things set straight before I continue developing on it.

 

[MajP]

This is an interesting conversation, and I would like fneily take on this because he has a strong theoretical background. I beleive that this structure is not normalized, but at the same time I have done similar things many times. There are times to use a non normal structure (efficiency, speed), but you need to be fully aware of the ramifications before going down that path.

I beleive that in proper DB theory, fk's in one table should only relate back to one table (Fneily, correct me if wrong). This means that if you have two types of appointments Program level appts (appt specific to a program) and General Level appts (appts specific to a person but not a program) then you should have actually 2 tables. TblGeneralAppts and TblProgramAppts. However, this looks weird because now I have two tables with the exact same information differing only in foriengn keys to different tables. I beleive that in theory is the correct design.

However, would I do it that way? Probably not, but I would make that decision understanding I am using a non normalized structure. Lets make this a little more interesting and assume that in our table we had Program Offices and they schedule appts as well and all appts will always have the same data fields. Now you would have 3 types of foriegn keys in your appt table and probably a field for apptType (general, Program, Division). Again I beleive that this is incorrect design, but I personally would still do it this way.

Fneily, your thoughts.

I know I had done similar with a Comments table. I had multiple Entities with Comments. All comments had
date Made
Originator
Memo
Resolution
etc.

At first I had multiple comment tables for different entities, but then combined them into one table for ease of development. Now I had a table with multiple FK to different tables and a Type field to tell me which table it related back. Pretty sure that violates normality, but in my case worked.

 

[lespaul]

I prefer to have a single key field for every table. Currently we are working with an old legacy system and we have multiple keyed tables and my joins look like this: (*note these are non-normalized tables so my joins are actually having to adjust for that issue too, but you can see the point)

FROM CMPHERMF H
INNER JOIN CMPDEFMF D ON H.CASPRE = D.CASPRE AND H.CASNUM = D.CASNUM AND H.DEFSEQ = D.DEFSEQ
INNER JOIN CMPCHGMF C ON H.CASPRE = D.CASPRE AND H.CASNUM = D.CASNUM AND H.DEFSEQ = C.DEFSEQ AND H.CHGSEQ = C.CHGSEQ

By having a single key field, that's only foreign key that needs to have a relationship. Queries are much clearer and easier to conceptualize.

FROM CMPHERMF H
INNER JOIN CMPDEFMF D ON H.DEFID = D.DEFID
INNER JOIN CMPCHGMF C ON H.CHGID = C.CHGID

Leslie

Have you met Hardy Heron?

 

[jeffshex]

So would I ditch that GeneralID key all together in that Appointment table and not worry about that relationship?
So that would make it:

tblGeneral      tblProgram         tblAppointments
----------      ----------         ---------------
PK GeneralID    PK ProgramID       PK AppntID
                FK GeneralID (12M) FK ProgramID(12M)

I would just always have to have the Program table involved to bring those 2 tables together whether I need anything out of that table or not.
How does that sound?

 

[fneily]

I'm going to trust you that a program is specific to a person and CAN NOT be used by anyone else. If so, then you have a many-to-many relationship which will change the table structures. And a person can have an appointment without a program treatment.
Ok. After getting more info, I'd do a version of MajP idea.
So:
tblGeneral ----> tblProgram one-to-many
GeneralID ProgramID
FirstName GeneralID
LastName Description
Etc.

tblAppointments
AppointmentID
SubjectID This will either be GeneralID or ProgramID
Date
Therapist
Location
etc.

So, in the form for tblAppointments, you could have two comboboxes, one for GeneralID and the other for ProgramID, to fill in SubjectID.
Now, through queries, setting the criteria for SubjectID, you can get all the appointment dates for a certain ProgramID. To get all the appointments for a person, you can use an OR criteria for the SubjectID. eg. PersonA Or PersonAProgram. This will also avoid the duplicate fields in two tables that MajP mentioned.
Seems to work. What do you think?

Also, MajP, as far as your type goes -general, Program, Division- that's fine as long as it's a field. General, Program, Division can be considered a category/table and some people make them individual columns. Drives me nuts.

I agree with both MajP and LesPaul.

 

[jeffshex]

Everyone has great points and definately good things to know for folks developing this stuff.
As for me I think I will go with the approach that I will remove the GeneralID from the appointments field due to the fact that anyone who has an appointment must be in a program.
So I will just use the Program table as the junction for the General and Appointments.
I just want to make sure I have this right, so correct me if I'm still off base here:
By doing it the way I described above, if I just want to query any appointments a certain person had, while not caring about the which program instance it's related to...I would still use all 3 tables in the query in order for it to pull correctly.

I appreciate all the input. This is good stuff.

 

[fneily]

due to the fact that anyone who has an appointment must be in a program". That one statement changes everything. It would be nice to get all information up front. Saves time.

tblGeneral ----> tblProgram -----> tblAppointment
GeneralID ProgramID AppointmentID
FirstName GeneralID ProgramID
Lastname Description Date

tblProgram is not a junction table.