Getting confused using many to many relationships 3

[macca007]

Hi all, recently i have table called tblTreatments, this contains what types of tumour treatments. However me manager has asked to create a seperate table for each particular type of tumour.

e.g

there will be tblTreatment and tblTumour1 and tblTumour2 where each tumour will have its own particular fields for that tumour. At the moment this is all displayed in tblTreatment.

Since the relationship will be: tblTreatment can have many tumours and tumours can hav many treatments.

I have been reading that u would need to use a junction table which will contain the primary key of tblTreatment and pk for tblTumour1. Wat else will be in junction table? Could u give me some examples fot things uve done off similar nature?

Cheers

 

[lupins46]

The fields that go into the junction table are for data that is for that treatment of that tumour.
It might be the quantity of a chemical or the number of treatments required; anything where the data about the treatment is not the same for all tumours to which it is applicable.
However, it does not sound like a good design move to create separate tables for different tumours. I would have thought the tumour type would be part of the data on a single tumours table. Of course, it is difficult to be definite about this without proper analysis, but I'm just giving my instant thought on the point.

 

[Crowley16]

the answer is nothing...

for EACH many to many relationship, in your case, EACH tumourType, you will need a link table that contains only 2 fields: treatmentPK, tumourPK

however this assumes that in each tumourType table, there exsists more than 1 tumourPK...

 

[lespaul]

Read 'The Fundamentals of Relational Database Design'. This is essential reading for anyone designing databases.

Leslie

 

[willir]

...Bascially, consider using one table

tblTumourType
TumourCode - primary key
TumourName
+ other stuff relavent to tumours
i.e. soft tissue, blood, etc

Having different tables for each table...
- high maintenance - every time you come across a different tumour to track, you have to develop a new table, form, report, etc.
- breaks the 2nd rule for Normalization

Categorizing tumours means you only have to add ONE RECORD.

To tie tumours to the unfortunate patients, you have to realize you have a many-to-many relationship...
- one patient can have multiple tumours
- many patients can have the same tumour
... This is your "junction" table.

tblDiagnosis
TumourCode - foreign key to tumour table
PatientID - foreign key to patient table

...Now for the tough part.
A patient can get a re-occurance of cancer so you may have to accommodate this unfortunate circumstance by including the date...

tblDiagnosis
TumourCode
PatientID
DiagDate

Primary key would be TumourCode + PatientID + DiagDate
(or drop the date field if you are not interested in this specific)

I suggested using a primary key of TumourCode instead of ID becasue use a meaningful code with a minimum of fuss, and if the code is sufficently descriptive, you can refer to the code instead of grabbing the full description.

Example:
BRST1 - first stage breast cancer
LYPH2
PANC4
etc...

Codes could be longer, and may include more "built-in" descriptions. This type of coding makes it easier for sorting, and minimizes joining tables later on.

You can of course use TumourID.

Presentation becomes pretty slick too. You can use the same table as the source of a subform that can depict diseases a patient has, and all the patients hainvg specific tumour.

Richard

 

[DanJR]

Richard,

How do you suggest handling the part where different tumours have different attributes? you didn't directly comment on this

Cheers,
Dan

 

[lespaul]

You would set that up the same way. You would have a table of all the attributes that any tumor could have:

tblAttributes
AttributeCode
(other information about the attribute)

tblTumorAttributes
TumorCode
AttributeCode

HTH

Leslie

 

[willir]

Bingo Leslie! And thank you for picking up the ball on my ommission.

You have a M:M between tumours and attributes...
- tumours can have more than one attribute
- one attribute can be exhibited in different tumours

Your "joiner" table is perfect.

Why use a M:M instead of a table for each tumour?
Well, I am sure as science advances, different attributes would be found. With a table designed for each tumour, you have to tweak the table, forms, reports, queries. By using a relational approach, you add one more attribute record.

...And Leslie, I am half way through Naked Empire. But I now see I have to wait for yet another tome. Sighhhh.

 

[lespaul]

<aside to Richard>
I think the next three books are going to be a "traditional" trilogy, where all the events and people come together and everything gets all wrapped up.

re: Jordan - WOT: I had decided to boycott the prequel that talks about Morraine and Lan because my thoughts were:
"Finish the main series before embarking on other projects", but then I saw an interview he gave and explained that there are going to be 2 other prequels, and that all of them are important to the story. So, I broke down and got it for 50% off and it wasn't bad. I sure hope he finishes the series before he DIES!

one more, have you read the George R. R. Martin series? That's another really good one, the next book is due out in a few weeks! Can't wait! He's a blood thirsty author, how can we find out all the secrets if he kills all the characters???

</aside to Richard>

Leslie

 

[willir]

...or before we die. Have a nice weekend!