locked
Database Design RRS feed

  • Question

  • I am designing a stock in and out system right now. There is a user table, order table, and stock-in table.

    Order table has a user Id column bonded with user table id column. Struct like below:

    user table                             order table

    id(primary key)--------               <g class="gr_ gr_47 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="47" id="47">temid</g>

                                   |

    name                        ------> <g class="gr_ gr_62 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="62" id="62">orderowner</g>

    now my question is, what if the user, for example, the user with id 1 was deleted, so what do I do with order table, there is no user can be queried because the user was deleted. How do I prevent this kind of problem? And if I want to check the order history of the user which deleted, how to design the DB?

    thanks in advance.


    • Edited by LeonXjm01 Monday, March 25, 2019 1:24 AM
    Monday, March 25, 2019 1:23 AM

Answers

  • Hi LeonXjm01,

    >>what if the user, for example, the user with id 1 was deleted, so what do I do with order table, there is no user can be queried because the user was deleted. How do I prevent this kind of problem? 

    If you have specified the foreign key constraint, it depends on the delete rules. While implementing update and delete operations on values in the parent table(user table), you should consider the impact on related values in the child table(order table). SQL Server provides different rules for managing the effect of updates and deletes on child table values. Please refer to SQL Server Foreign Key Update and Delete Rules.

    >>And if I want to check the order history of the user which deleted, how to design the DB?

    If you are using SQL Server 2016 or later, you can use a database feature called temporal tables. A system-versioned temporal table is a type of user table designed to keep a full history of data changes and allow easy point in time analysis. Please refer to Temporal Tables

    Best Regards,
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, March 25, 2019 2:44 AM
  • Hi,

    You need to create a trigger on user table on delete and if anyone tries to delete a user with id existing in order table just give an error message and rollback transaction. If you still want to delete that user then need to cascade it to order table and delete corresponding records for that id in that table.

    Mark as answer if it helps. Thanks. 







    Monday, March 25, 2019 7:43 AM

All replies

  • Hi LeonXjm01,

    >>what if the user, for example, the user with id 1 was deleted, so what do I do with order table, there is no user can be queried because the user was deleted. How do I prevent this kind of problem? 

    If you have specified the foreign key constraint, it depends on the delete rules. While implementing update and delete operations on values in the parent table(user table), you should consider the impact on related values in the child table(order table). SQL Server provides different rules for managing the effect of updates and deletes on child table values. Please refer to SQL Server Foreign Key Update and Delete Rules.

    >>And if I want to check the order history of the user which deleted, how to design the DB?

    If you are using SQL Server 2016 or later, you can use a database feature called temporal tables. A system-versioned temporal table is a type of user table designed to keep a full history of data changes and allow easy point in time analysis. Please refer to Temporal Tables

    Best Regards,
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, March 25, 2019 2:44 AM
  • Thank you, I am currently using SQL SERVER 2012, but I would try 2016, thanks

    Monday, March 25, 2019 7:30 AM
  • Hi,

    You need to create a trigger on user table on delete and if anyone tries to delete a user with id existing in order table just give an error message and rollback transaction. If you still want to delete that user then need to cascade it to order table and delete corresponding records for that id in that table.

    Mark as answer if it helps. Thanks. 







    Monday, March 25, 2019 7:43 AM