2

So I'm struggling with a database concept a new project.

I need a database for events which can have relations between them and they have to be executed at a specific time.

Example:

id: 1, user_from: 1, user_to: 2, when: 08:00

id: 2, user_from: 2, user_to: 1, when 07:30

id: 3, user_from: 3, user_to: 2, when: 07:00

If user with id 1 is logging in at 08:01, every single event with relations to the user with id 1 has to be executed.

The problem in these example:

With this example, all three events should have been executed, because 1 has a relation with 2 and 2 has relations with 3. If I would change the event with id 3 to something like this:

id: 3, user_from: 3, user_to: 2, when 09:00

the event with id 3 shouldn't have been executed, because the 'when'-index is just at 08:01.

Another problem:

If user with id 3 is logging in at 09:00 only the event with id 3 should have been executed.

My Question:

With the possibility that there are more then 10k events at the same time, but only one for the user who is logging in, I don't want to calculate the results of all events, I just want the ones with a relation to the user which is logging in. How can I do this to prevent fast loading all the time?

My ideas:

MongoDB with Recursive Query:

Recursive search on a collection in MongoDB

Normal SQL Database with a relation table:

I will calculate all relations when the new event is added to the events table, so I don't need the care about them when the user is logging in.

Thank you for any responses and feel free to ask any questions about the problem. I'm also open for any new title suggestion. It's hard to describe...

Community
  • 1
  • 1
Janick Fischer
  • 651
  • 7
  • 17
  • You could execute every event based on time and do not bother with the relation also. – PeterRing Mar 02 '17 at 14:18
  • Thank you for your answer, but the problem with this solution is, that it's possible that there are more then 10k events on the same time... There will be a worker who works down some events, but if a user is logging in, his events with all relations have a higher priority. – Janick Fischer Mar 02 '17 at 14:58
  • Can't you use triggers to launch events when specifict things happen? – Snowlockk Mar 02 '17 at 15:39

1 Answers1

0

Separate these two things: The raw data. The business logic that interprets the data. It may not be wise to do any of the logic in the database.

For the database side of things, you seem to have one table with 3 columns: (user_from, user_to, when). If there is only one when between user_from and user_to, then you don't need an id.

The algorithm of what to do when probably need to be written in your application language. And it may need to fetch the entire table before processing. And it sounds like this algorithm needs to be applied at login time.

Loading 10K rows is pretty trivial. Since you have "indirect" references to events (user1 -> user2 -> eventx), it would probably be more costly to follow the 'tree' in the database than in the app language.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • First of all, thank you for your answer. I think the only way to get to a solution is to test it you. I will try to create a solution with a complete loading of the event table and follow the 'tree' in the app language and one with a relation table. – Janick Fischer Mar 03 '17 at 08:34