One-Many design fault in data return; bad aggregate or bad data structure, or both!?
Application Type
Traditional Web


I have three tables structured as exampled below:

IE: FieldName (Example attributes)

DocID (1,2)
Name (Infusion A, Infusion B)
Speciality (10,11)
Category (15,16)

ID (10)
Name (Cardiology)

ID (15,16)
Speciality (10)
Name (Guidelines, Clinics)

I simply wish to populate a table like

Speciality  | Category | Name
Cardiology | Guidelines | Infusion A
Respiratory| Clinics| Infusion B

However... When the tables are populated with the example data above, I get a response like this (It looks like the categories table returns both rows in the table for each of the index entries)...

Speciality  | Category | Name
Cardiology | Guidelines | Infusion A
Cardiology| Clinics| Infusion A
Cardiology | Guidelines | InfusionB
Cardiology| Clinics| InfusionB

My aggregates are the three tables as sources, and then joined like this

Index.Speciality = Specialities.ID
Categories.Speciality = Specialities.ID
Index.Speciality = Categories.Speciality

Would be grateful for your direction.
Thank you



I think your Index entity would only need the CategoryId, because then based on that CategoryId you could reach the Specialty.

Then the joins would be like this:

Specialty.Id = Category.SpecialtyId
Category.Id = Index.CategoryId

This would mean that a Specialty would be in a relationship 1-to-many with Categories and Categories would be in relationship 1-to-many with Indexs.

Is this what you want to achieve? it seems right based on the values you say you want to enter!

Tell me if this helps! Cheers,

Hi Paulo.

Thank you. That works great.

I'm not sure what on earth I was trying to do earlier...

Thanks for taking the time to reply. 

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.