Database or entity modelling for a list page that will appear in all users

Hi Outsystems community, I wanted to ask specifically about entity or database design for my case.

So we are going to create a notification page for all users, any users are able to access this page with features. The features that users are capable with:

1. delete the list item (record) notifications

2. read the list item (flagging the record) notifications after click the detail, i managed this one with data fetch in the page to change the flag

here's the sneak peek for the page:

the first item is when the user is not read the item yet, the second one does. about our database design for the page, is look like this:

But when I try to open the page with another User account, the list is not appeared after joining the entities.. i realized because in the filters i used this syntax: 

NotificationTracking.UserId = GetUserId() or NotificationTracking.UserId = NullIdentifier()

so after the list item is flagged as read, it won't appear in the other Users page. i'm assuming it's because the entity model is incorrect. any suggestion or advises so the all the features above i mentioned can be achieved? do i need another entity for my case? or i have to insert the records using For Each (loop) to all users? 

Thanks before, any answers will be appreciated

Hi Aditya,

your model looks like a good start to me.  Don't see why that woudn't support you use case.  So I think the problem must be in either the creation of the NotificationTracking entity (it simply isn't there for all users) or in the way you developed that screen, for example marking all of them as read or deleted when one user clicks.

So let's start by the first, by what process do notifications get created, and how do you make sure that a NotificationTracking is created for all users.  So maybe check, this second user you are now logging on with, If you look in the database, is there a NotificationTracking there for him.

And to see that you didn't mark all of them as deleted, is the IsRead and IsDeleted flag still on False for this second user ?

If that all looks ok, can you share the oml, so whe can see what you are doing.

Also, another remark, why would NotificationTracking.UserId ever be NullIdentifier ?

Dorine

Dorine Boudry wrote:

Hi Aditya,

your model looks like a good start to me.  Don't see why that woudn't support you use case.  So I think the problem must be in either the creation of the NotificationTracking entity (it simply isn't there for all users) or in the way you developed that screen, for example marking all of them as read or deleted when one user clicks.

So let's start by the first, by what process do notifications get created, and how do you make sure that a NotificationTracking is created for all users.  So maybe check, this second user you are now logging on with, If you look in the database, is there a NotificationTracking there for him.

And to see that you didn't mark all of them as deleted, is the IsRead and IsDeleted flag still on False for this second user ?

If that all looks ok, can you share the oml, so whe can see what you are doing.

Also, another remark, why would NotificationTracking.UserId ever be NullIdentifier ?

Dorine

 

Hi Dorine, thanks for your response.

The records of Notification entity is created when an Exposed REST API is triggered, the field come from other resource/application and will be stored to Outsystems entity.

"and how do you make sure that a NotificationTracking is created for all users"

No I haven't, so I have a concept in mind when a user read the detail, the record just created after clicking it.. turns out it didn't work with the database model I created. The NotificationTracking is empty, there is no record until the Users execute an action (read) for themselves.

That is why I put this syntax in the filter: "NotificationTracking.UserId = NullIdentifier .."

So is your suggestion I have to create a loop function for creating the list for all users?

Solution

Well,

you don't necessarily need to loop and pre-create a notification-tracking for each user.  I think it is what I would do, so I can for example easily query what users haven't read their notifications yet and things like that.  

But then you should also think about new users, what old notifications do you still want them to see.  If a loop is used at time of arrival in the system of a notificaiton, only currently known users will get them.  (well, I guess that's something to think about with any solution you choose, if you let users see all unseen when opening app, then a new users gets overwhelmed with a lot of old notifications that are not relevant anymore.)

So if you don't loop through current users and create a notification-tracking, you have to have an aggregate that fetches all unseen notifications.  So I understand that you tried to do that with the filter of "notification.userid=nullidentifier" but that won't work.

I'm not seeing your aggregate, but I think you have in sources notification and notification-tracking, joined by a WithOrWithout, so retrieving all notifications, and the matching notification-trackings if they exist.

What this join does is

  • if no notification-tracking exists yet at all for a given notification, it will produce a row in the resultset where notification side is filled, notification-tracking side is empty.  
  • If one or more notification-tracking exist for a given notification, it will produce all found combinations of notification with notification-tracking, but will no longer produce a row in the resultset with an empty notification-tracking side.  

That's what the WithOrWithout does.  After that, your filter is applied.  So you can see that in the first situation, your filter still hits because there's a row with an empty NotificationTracking, meaning that the UserId in it will be NullIdentifier.  You can also see that in the second situation, as it no longer produces a row with empty NotificationTracker, and not a row yet for your currently logged on user, none of the rows will satisfy your filter.

So what you need, is to modify your join condition instead of having a filter.  So remove the filter completely.

And modify the join condition between Notification and NotificationTracking as follows


Notification   WithOrWithout  NotificationTracking

where

Notification.Id = NotificationTracking.NotificationId

and

NotificationTracking.UserId = GetUserId()


It's hard to say exactly without seeing your aggregate, but it should be something like this.

The difference is as follows

let's say this is your data

Notification                                NotificationTracking

__________                             ________________

id 1                                               id 1   user 1        notificaton 1

id 2


With a filter

the join will produce these 2 rows

Notification 1          NotificationTracking 1

Notification 2          NotificationTracking empty


On this you apply your filter, for user 1 it will give both rows, row 1 for correct user, row 2 for empty user

But for user 2, it will only give row 2, for empty user, notification 1 will never be shown to user2


With a join condition

the join will produce these rows for user 1

Notification 1         NotificationTracking 1  

because fits condition

Notification 2         NotificationTracking empty 

because it's with or without, so if right join side doesnt exist, still produces a row with data from left side

the join will produce these rows for user 2

Notification 1         NotificationTracking empty  

because it's with or without, so if right join side doesnt exist, still produces a row with data from left side

Notification 2         NotificationTracking empty 

because it's with or without, so if right join side doesnt exist, still produces a row with data from left side


It's a bit hard to explain, hope you understand,

Dorine

See attached oml for an example where both are compared side by side

Solution

Hi Dorine, thank you so much for your further explanation, I really appreciate it :)


Sorry I don't attach/screenshot my aggregate before, but you already guessed it right! Well here's my aggregate:

And yes, for now there is only one filter in early development, it is: NotificationTracking.UserId = GetUserId() or NotificationTracking.UserId = NullIdentifier()

True, this aggregate caused the list did not appear in the other user because it's already taken for another user, hence with that filter. I tried with your solution in the join condition:

"Notification   WithOrWithout  NotificationTracking

where

Notification.Id = NotificationTracking.NotificationId

and

NotificationTracking.UserId = GetUserId()"


welp.. it did work! thank you :) now the list is showed with different user. also thanks for the OML you attached.

But anyway can I have your opinion, I have two things in mind for delete feature notification list in each user:

  1. keeping this database model and continue with this join condition you suggested, and for delete feature in this page the records did not actually "deleted" from the entity, instead it just flagged as "deleted". so in the aggregate, i probably going to put a filter for that flag. is it going to cost performance if we load the page? Imagine if there is one thousand records in the aggregate for a single user but of course, before filtered.
  2. change the database model and stick with creating a loop function for each user to have their own records in the entity. about the new user, maybe i could create a Timer function that running every 00:00 AM in the server for the process to start?

So yeah, which one do you think is better? For long term and the user experience? Once again, thanks before

@1. no real performance problems I think, 1000 records is not much.  Also, at some point you could consider deleting old Notifications and associated NotificationTrackings to keep total database size small (maybe anything older than x months)  If you keep this join to list notifications to your user, it's not even an option to physically delete them, as then the systems doesn't know if they have ever been seen by the user, so would show them.  So yes, just add the filter (a real filter this time :-)

@2. is also possible, it would still be good to keep the NotificationTracking per user with a IsDeleted flag rather than really delete them when user doens't want to see anymore.  Just to be able to still know what notifications they have been presented with.  Here again, only really throw away when notification itself is cleared after x months.  I'm not sure what you would do with a timer, if you want to pre-create them, why not do it at the same time you create the notification and possibly at the time you create a new user?


But for both solutions, you have to answer this question : what notifications do I want to present to a new user of the system.  With solution 1, you don't have a lot of control, as the query will automatically show every notification in the system, even if it's months old.  with solution 2, you can decide in your logic of user creation, maybe create no tracking record for pre-existing notifications, or maybe only pre-create trackings for a new user of notifications that have a creation date of last x days.


Good luck,

Dorine


Oh, just an unrelated remark, I personally would als want to track things like, when was the first time a user read it, when did he delete it, so not only flags.

"I'm not sure what you would do with a timer, if you want to pre-create them, why not do it at the same time you create the notification and possibly at the time you create a new user?"

Yes, I mean every time the Notification creation is triggered, I want to create the NotificationTracking too at the same time. About triggering the process when a new user is created I don't think it's a good idea for us. We use ADAL for login authentication, I assumed it's going to take a lot of time when logging in occurred for the new user. Resulting in bad experience. That's why I asked about using Timer process.


"But for both solutions, you have to answer this question : what notifications do I want to present to a new user of the system.  With solution 1, you don't have a lot of control, as the query will automatically show every notification in the system, even if it's months old.  with solution 2, you can decide in your logic of user creation, maybe create no tracking record for pre-existing notifications, or maybe only pre-create trackings for a new user of notifications that have a creation date of last x days."

Do you mean having a Notification item expiration system? So when the item is long enough (ex: after x month) what will happened to that set of records? I don't decide this yet, depending on our client's requirement, but if it's needed I probably going to use Timer process. Every record that meets condition will be eliminated.


Thanks again for your advises Dorine, I really appreciate it!


"About triggering the process when a new user is created I don't think it's a good idea for us."  

Ok, a timer makes sense to keep things simple.  But in your current design, there's no need for that.  One of the things you could do to control better what new users get to see, is in your aggregate, compare creation date of notifications to creation date of user, and only show notifications that were created no more than x days before user was created.

"Do you mean having a Notification item expiration system? "

Yes, that's what I meant, a timer sounds right.