locked
How to design two same pattern of table RRS feed

  • Question

  • I need two tables(reminder_msg_template and recall_msg_template) which have same pattern.

    Those two tables' format is like following.

    -------------------------------------

    message_no       INT

    message_body   VARCHAR(255)

    comment           VARCHAR

    -------------------------------------

    To design, I can design reminder and recall table with the same format like upper.

    However I can design only one table(msg_template) and add type field to decide reminder or recall like following.

    -------------------------------------

    message_no       INT

    message_body   VARCHAR(255)

    comment           VARCHAR

    type                   INT     //1: reminder  2: recall

    -------------------------------------

    Which one is regulation in DB design?

    Sunday, March 22, 2020 12:27 AM

Answers

  • One table is normally better than 2 especially if you don't anticipate structural changes in either of them. If you also don't anticipate lots of different types I would make type column to be tinyint instead of int.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi NEditor Sunday, March 22, 2020 2:05 AM
    • Marked as answer by Jeff0803 Sunday, March 22, 2020 4:24 AM
    Sunday, March 22, 2020 2:05 AM
    Answerer

All replies

  • One table is normally better than 2 especially if you don't anticipate structural changes in either of them. If you also don't anticipate lots of different types I would make type column to be tinyint instead of int.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi NEditor Sunday, March 22, 2020 2:05 AM
    • Marked as answer by Jeff0803 Sunday, March 22, 2020 4:24 AM
    Sunday, March 22, 2020 2:05 AM
    Answerer
  • That's a not a question with a straightforward answer. From what you say I would probably go with one table. But let's say by time you find that there are columns you need to add that apply only to reminder templates. And other columns that apply only to recall templats. Now you may regret the original decision. On the other hand, if you find that you need to add four more templates with the same columns,  you will count yourself lucky that you went with one table originally.

    One way to look at this is that in a relational database a table is supposed to model a unique entity. But what is the entity here? Is just "template", or is it the type of template?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by Naomi NEditor Sunday, March 22, 2020 8:29 PM
    Sunday, March 22, 2020 7:56 PM