2

There are two table User & Todo. One User has many Todo & one Todo belongs to One User. user_id & todo_id is unique.

Todo table (todo_id, user_id, todo)

  • 1, 1, 'todo1'
  • 2, 1, 'todo2'

User table (user_id, name, email)

  • 1, name1, email1
  • 1, name2, email2

I need some kind of database architecture to store all changes in same table (or) may be better way without compromising constaint like unique.

  • See here: http://stackoverflow.com/questions/13643023/add-unique-constraint-in-sql-server-2008-gui , something like `alter table todotbl add constraint ix_todo unique nonclustered (user_id,todo_id)` should work. – Carsten Massmann Jan 14 '17 at 07:42
  • You have duplicate ID of `1` on both rows of your User table, violating your Unique constraint. – Basil Bourque Jan 14 '17 at 07:56
  • What do you mean by “store all changes in same table”? Do you mean keeping a history or audit trail of each change to the row’s values? Your Question is not at all clear. – Basil Bourque Jan 14 '17 at 08:00
  • @BasilBourque something like audit log. But ease to query table, have constraint in table like soft deletion across children. –  Jan 14 '17 at 09:22

0 Answers0