Aggregate Design
Application Type
Traditional Web

I wonder if someone might be able to help me with my aggregate design, please?

I have the following tables

SMS_History
Type (Referral or standard)
Phone_Number
DateTime_Sent
Content

Referral_Leaflets
Link URL
Name

Customers
Name
Number

Standard_Leaflets
Link URL
Name

The SMS_History table contains a list of all the messages ever sent. Every record in the SMS_History table will have a corresponding entry in the customers table.

The SMS_History table, will either refer to a Referral_Leaflet entry OR a Standard_Leaflet entry.

I wish to create an output like this:

Datetime_Sent | Customer Name | Type | Detail
10/05/2022 @ 1920 | Mr Smith | Referral | Service 1
10/05/2022 @ 1900 | Mr Singh | Standard | Link 1

I obviously have all four tables in the aggregate, and have set the joins like this:

Sms_History.number = Customers.Number (Only with)
Sms_History.Content like "%" + Referral_Leaflets.Link + "%" (With or without)
Sms_History.Content like "%" + Standard_Leaflets.Link " "%" (With or without)

This doesn't work...

It gives an output like this:

Datetime_Sent | Customer Name | Type | Detail
10/05/2022 @ 1925 | Mr Smith | Referral | Service 1
10/05/2022 @ 1925 | Mr Smith | Referral | Service2
10/05/2022 @ 1925 | Mr Smith | Referral | Service3
10/05/2022 @ 1920 | Mr Singh | Standard | Service 1
10/05/2022 @ 1920 | Mr Singh| Standard| Service 2
10/05/2022 @ 1920 | Mr Singh | Standard | Service3

I would appreciate any direction.

Thank you

Hi @Leigh Rogers ,

First improvement: if your History entity has either a referral OR a Standard, and both have same attributes, you don't need two entities for that. You can make a static entity Leaflets that has two records, Referral and Standard. Then in your SMS_History you should have an attribute of type Leaflets Identifier.

Next, I need to understand what are you trying to filter by? You want to get only records of a specific date? specific customer?
Also, what is the detail collumn on your aggregate? i don't see that attribute in any of the tables.

I need to better understand what you want to filter by and achieve with your aggregate, provide more info so I can help!

Cheers,
Paulo

I agree that the design would be better with the one entity, but this is utilising an external database structure, unfortunately, and effectively trying to join two systems, hence the two tables.

I'm not trying to filter by anything, really, I just want a list of SMS messages, and the types.

The detail column in the aggregate is the service type. For Referral SMS, this could be Clinic, Scan or routine appointment. For Standard SMS, this could be Welcome SMS, advice leaflet, or something else.

I am in a position to rebuild the SMS_History entity, but this means I'd have to edit the SMS api's on the other systems, and do work on other systems too, and I'd rather avoid this if I can! It's something I'll have to do eventually though...

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