We have a requirement that presents certain items for users that they can add to their shopping cart. There are common elements among users, but users can add / edit common choice names.
- Common elements are: apple, banana
- User 1: Mango
- User 2: Papaya
When the database receives queries, User1 must have Apple, Banana, Mango, and User2 should have Apple, Banana, Papaya.
But User2 can also edit "Banana" so that the title becomes "Plaintain". So for User2, "Plaintain" would be returned but for User1, the word "Banana" is still used.
Approaches envisaged up to now
We have considered two models for this:
Create two tables,
TableCommonChoicescontains "common" choices while
TableUserChoicescontains each entry in
UserIdfor a specific user.
When creating a new user, the database will create individual entries for the new user based on the entries of
TableCommonChoices. In this way, users each have examples of common choices that can be edited and also add their own choices.
A concern here is whether a new common choice is added in the first array. It presents additional complexity when adding new common choices for each user in the other table. Ditto for the update, delete.
Will always have two tables,
TableUserChoices. But the second table will be used to store "Delta" changes in current choices and new entries.
When creating a new user, nothing is created in
TableUserChoices. Once the user changes a common choice, it's the only time an entry is
TableUserChoiceswill be created containing the & # 39; delta & # 39; and the reference identifier of the modified choice.
When a new common choice is added, it is only once, because the database will always look at the
TableCommonChoicesas common for all users.
- What is the best design for this requirement?
- And, is there a term for the second reason?