Repeated events in calendar












1















First of all, please excuse my perfectible English, I am French.



I'm coming to ask for some advice on a database design problem.



I have to design a calendar with events. Briefly, an event includes a start date/time, an end date/time, and a description.



The problem is that I have to consider repetitions; it is possible when creating an event to indicate that it starts next week and repeats itself until a specific date or not.



I see two possibilities of design:




  1. create an events table with id, start_datetime, end_datetime and description fields.
    When adding a new event, we generate as many rows as there are repeated events.


Advantages: we can make a SELECT * to retrieve all the events, without particular algorithm. In addition, it is possible to modify the descriptions of each occurrence of an event, insofar as they are considered as all different.



Disadvantage (MAJOR!): If we do not put an end date to have an infinite repetition, we will not memorize an infinity of events...




  1. take inspiration from the method described on this thread, that is to say two tables:



    • events table





id description
1 Single event on 2018-11-23 08:00-09:30
2 Repeated event :
* every monday from 10:00 to 12:00 from Monday 2018-11-26
* every wednesday from 2018-11-28 from 14:00 to 14:45 until 2019-02-27




  • event_repetitions table



id event_id start_datetime end_datetime interval end_date
1 1 2018-11-23 08:00:00 2018-11-23 09:30:00 NULL NULL
2 2 2018-11-26 10:00:00 2018-11-26 12:00:00 604800 NULL
3 2 2018-11-28 14:00:00 2018-11-28 14:45:00 604800 2019-02-27


Note: interval is the number of seconds between each occurrence, 604800 = 24 (hours) * 3600 (seconds) * 7 (days).



Advantage: In the case of infinite repetitions (case of the event of id 2), we have very few lines to write and performances are increased.

Disadvantages: if we want to modify the description of the event (or other possible fields) for a specific occurrence and not another, we can not without creating a third table, event_descriptions for example:




id event_id user_id datetime description
1 2 1 2018-11-26 10:00:00 Comment from 2018-11-26
2 2 2 2018-12-03 10:00:00 Comment of the second occurrence, i.e. from 2018-12-03


Note: user_id is the logged-in user who wrote the comment.



Another disadvantage is that to get the list of events for a given day, week, or month, the selection query will be more complex and use joins. The event_descriptions table may, when there are hundreds of thousands of events, be very big.



My question is: what would you recommend as a more effective alternative? Maybe the second solution is good? What do you think?



In terms of technologies used, I intend to go on MySQL, the DBMS I know best. Nevertheless, if you think that using for example MongoDB is better in case of very large numbers of lines, do not hesitate to report it.



For information, my application is an API developed with API Platform, so Symfony 4 with Doctrine ORM.



Thank you in advance for your answers.










share|improve this question

























  • IMHO go for solution 2 as it's more accurate then a simple solution is to add the field "description" to event_repetion table and remove it form event table. For db don't go for NoSQL as you data seems very relational, no need to add the complexity of handling data integrity yourself

    – mboullouz
    Nov 29 '18 at 12:32


















1















First of all, please excuse my perfectible English, I am French.



I'm coming to ask for some advice on a database design problem.



I have to design a calendar with events. Briefly, an event includes a start date/time, an end date/time, and a description.



The problem is that I have to consider repetitions; it is possible when creating an event to indicate that it starts next week and repeats itself until a specific date or not.



I see two possibilities of design:




  1. create an events table with id, start_datetime, end_datetime and description fields.
    When adding a new event, we generate as many rows as there are repeated events.


Advantages: we can make a SELECT * to retrieve all the events, without particular algorithm. In addition, it is possible to modify the descriptions of each occurrence of an event, insofar as they are considered as all different.



Disadvantage (MAJOR!): If we do not put an end date to have an infinite repetition, we will not memorize an infinity of events...




  1. take inspiration from the method described on this thread, that is to say two tables:



    • events table





id description
1 Single event on 2018-11-23 08:00-09:30
2 Repeated event :
* every monday from 10:00 to 12:00 from Monday 2018-11-26
* every wednesday from 2018-11-28 from 14:00 to 14:45 until 2019-02-27




  • event_repetitions table



id event_id start_datetime end_datetime interval end_date
1 1 2018-11-23 08:00:00 2018-11-23 09:30:00 NULL NULL
2 2 2018-11-26 10:00:00 2018-11-26 12:00:00 604800 NULL
3 2 2018-11-28 14:00:00 2018-11-28 14:45:00 604800 2019-02-27


Note: interval is the number of seconds between each occurrence, 604800 = 24 (hours) * 3600 (seconds) * 7 (days).



Advantage: In the case of infinite repetitions (case of the event of id 2), we have very few lines to write and performances are increased.

Disadvantages: if we want to modify the description of the event (or other possible fields) for a specific occurrence and not another, we can not without creating a third table, event_descriptions for example:




id event_id user_id datetime description
1 2 1 2018-11-26 10:00:00 Comment from 2018-11-26
2 2 2 2018-12-03 10:00:00 Comment of the second occurrence, i.e. from 2018-12-03


Note: user_id is the logged-in user who wrote the comment.



Another disadvantage is that to get the list of events for a given day, week, or month, the selection query will be more complex and use joins. The event_descriptions table may, when there are hundreds of thousands of events, be very big.



My question is: what would you recommend as a more effective alternative? Maybe the second solution is good? What do you think?



In terms of technologies used, I intend to go on MySQL, the DBMS I know best. Nevertheless, if you think that using for example MongoDB is better in case of very large numbers of lines, do not hesitate to report it.



For information, my application is an API developed with API Platform, so Symfony 4 with Doctrine ORM.



Thank you in advance for your answers.










share|improve this question

























  • IMHO go for solution 2 as it's more accurate then a simple solution is to add the field "description" to event_repetion table and remove it form event table. For db don't go for NoSQL as you data seems very relational, no need to add the complexity of handling data integrity yourself

    – mboullouz
    Nov 29 '18 at 12:32
















1












1








1








First of all, please excuse my perfectible English, I am French.



I'm coming to ask for some advice on a database design problem.



I have to design a calendar with events. Briefly, an event includes a start date/time, an end date/time, and a description.



The problem is that I have to consider repetitions; it is possible when creating an event to indicate that it starts next week and repeats itself until a specific date or not.



I see two possibilities of design:




  1. create an events table with id, start_datetime, end_datetime and description fields.
    When adding a new event, we generate as many rows as there are repeated events.


Advantages: we can make a SELECT * to retrieve all the events, without particular algorithm. In addition, it is possible to modify the descriptions of each occurrence of an event, insofar as they are considered as all different.



Disadvantage (MAJOR!): If we do not put an end date to have an infinite repetition, we will not memorize an infinity of events...




  1. take inspiration from the method described on this thread, that is to say two tables:



    • events table





id description
1 Single event on 2018-11-23 08:00-09:30
2 Repeated event :
* every monday from 10:00 to 12:00 from Monday 2018-11-26
* every wednesday from 2018-11-28 from 14:00 to 14:45 until 2019-02-27




  • event_repetitions table



id event_id start_datetime end_datetime interval end_date
1 1 2018-11-23 08:00:00 2018-11-23 09:30:00 NULL NULL
2 2 2018-11-26 10:00:00 2018-11-26 12:00:00 604800 NULL
3 2 2018-11-28 14:00:00 2018-11-28 14:45:00 604800 2019-02-27


Note: interval is the number of seconds between each occurrence, 604800 = 24 (hours) * 3600 (seconds) * 7 (days).



Advantage: In the case of infinite repetitions (case of the event of id 2), we have very few lines to write and performances are increased.

Disadvantages: if we want to modify the description of the event (or other possible fields) for a specific occurrence and not another, we can not without creating a third table, event_descriptions for example:




id event_id user_id datetime description
1 2 1 2018-11-26 10:00:00 Comment from 2018-11-26
2 2 2 2018-12-03 10:00:00 Comment of the second occurrence, i.e. from 2018-12-03


Note: user_id is the logged-in user who wrote the comment.



Another disadvantage is that to get the list of events for a given day, week, or month, the selection query will be more complex and use joins. The event_descriptions table may, when there are hundreds of thousands of events, be very big.



My question is: what would you recommend as a more effective alternative? Maybe the second solution is good? What do you think?



In terms of technologies used, I intend to go on MySQL, the DBMS I know best. Nevertheless, if you think that using for example MongoDB is better in case of very large numbers of lines, do not hesitate to report it.



For information, my application is an API developed with API Platform, so Symfony 4 with Doctrine ORM.



Thank you in advance for your answers.










share|improve this question
















First of all, please excuse my perfectible English, I am French.



I'm coming to ask for some advice on a database design problem.



I have to design a calendar with events. Briefly, an event includes a start date/time, an end date/time, and a description.



The problem is that I have to consider repetitions; it is possible when creating an event to indicate that it starts next week and repeats itself until a specific date or not.



I see two possibilities of design:




  1. create an events table with id, start_datetime, end_datetime and description fields.
    When adding a new event, we generate as many rows as there are repeated events.


Advantages: we can make a SELECT * to retrieve all the events, without particular algorithm. In addition, it is possible to modify the descriptions of each occurrence of an event, insofar as they are considered as all different.



Disadvantage (MAJOR!): If we do not put an end date to have an infinite repetition, we will not memorize an infinity of events...




  1. take inspiration from the method described on this thread, that is to say two tables:



    • events table





id description
1 Single event on 2018-11-23 08:00-09:30
2 Repeated event :
* every monday from 10:00 to 12:00 from Monday 2018-11-26
* every wednesday from 2018-11-28 from 14:00 to 14:45 until 2019-02-27




  • event_repetitions table



id event_id start_datetime end_datetime interval end_date
1 1 2018-11-23 08:00:00 2018-11-23 09:30:00 NULL NULL
2 2 2018-11-26 10:00:00 2018-11-26 12:00:00 604800 NULL
3 2 2018-11-28 14:00:00 2018-11-28 14:45:00 604800 2019-02-27


Note: interval is the number of seconds between each occurrence, 604800 = 24 (hours) * 3600 (seconds) * 7 (days).



Advantage: In the case of infinite repetitions (case of the event of id 2), we have very few lines to write and performances are increased.

Disadvantages: if we want to modify the description of the event (or other possible fields) for a specific occurrence and not another, we can not without creating a third table, event_descriptions for example:




id event_id user_id datetime description
1 2 1 2018-11-26 10:00:00 Comment from 2018-11-26
2 2 2 2018-12-03 10:00:00 Comment of the second occurrence, i.e. from 2018-12-03


Note: user_id is the logged-in user who wrote the comment.



Another disadvantage is that to get the list of events for a given day, week, or month, the selection query will be more complex and use joins. The event_descriptions table may, when there are hundreds of thousands of events, be very big.



My question is: what would you recommend as a more effective alternative? Maybe the second solution is good? What do you think?



In terms of technologies used, I intend to go on MySQL, the DBMS I know best. Nevertheless, if you think that using for example MongoDB is better in case of very large numbers of lines, do not hesitate to report it.



For information, my application is an API developed with API Platform, so Symfony 4 with Doctrine ORM.



Thank you in advance for your answers.







doctrine-orm calendar database-schema database-performance api-platform.com






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 28 '18 at 15:34







Marc Diaz

















asked Nov 28 '18 at 15:24









Marc DiazMarc Diaz

63




63













  • IMHO go for solution 2 as it's more accurate then a simple solution is to add the field "description" to event_repetion table and remove it form event table. For db don't go for NoSQL as you data seems very relational, no need to add the complexity of handling data integrity yourself

    – mboullouz
    Nov 29 '18 at 12:32





















  • IMHO go for solution 2 as it's more accurate then a simple solution is to add the field "description" to event_repetion table and remove it form event table. For db don't go for NoSQL as you data seems very relational, no need to add the complexity of handling data integrity yourself

    – mboullouz
    Nov 29 '18 at 12:32



















IMHO go for solution 2 as it's more accurate then a simple solution is to add the field "description" to event_repetion table and remove it form event table. For db don't go for NoSQL as you data seems very relational, no need to add the complexity of handling data integrity yourself

– mboullouz
Nov 29 '18 at 12:32







IMHO go for solution 2 as it's more accurate then a simple solution is to add the field "description" to event_repetion table and remove it form event table. For db don't go for NoSQL as you data seems very relational, no need to add the complexity of handling data integrity yourself

– mboullouz
Nov 29 '18 at 12:32














1 Answer
1






active

oldest

votes


















0














I allow myself to do a little up, hoping other answers.






share|improve this answer























    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53522813%2frepeated-events-in-calendar%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0














    I allow myself to do a little up, hoping other answers.






    share|improve this answer




























      0














      I allow myself to do a little up, hoping other answers.






      share|improve this answer


























        0












        0








        0







        I allow myself to do a little up, hoping other answers.






        share|improve this answer













        I allow myself to do a little up, hoping other answers.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Dec 3 '18 at 7:59









        Marc DiazMarc Diaz

        63




        63
































            draft saved

            draft discarded




















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53522813%2frepeated-events-in-calendar%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            A CLEAN and SIMPLE way to add appendices to Table of Contents and bookmarks

            Calculate evaluation metrics using cross_val_predict sklearn

            Insert data from modal to MySQL (multiple modal on website)