0

What is the best practice to design one business model into a multiple tables using one to one relationship? Lets say I Have the following diagram; Demo Diagram

Both tables are representing ONE business object/entity which is the Person. For table PersonNationalId is it a best practice is to

  1. Add addition column PersonNationalId to be the primary key
  2. Remove the unique key and mark the PersonId as the Primary key? (I recommend this solution)
  3. It's good like that with the unique key and we don't have to add any primary keys?

I'm not considering breaking the business to two/multiple tables because I update each table from a different screen in my system and I want to track the changes (through CreatedBy and CreatedDate columns in each table)

What is the best practice from the previous choices/solutions? if you any additional solution I will be happy to know it.

Thanks in advance

Ahmed Magdy
  • 5,956
  • 8
  • 43
  • 75

2 Answers2

1

Documents expire, and when they do, new ones are issued with a new number. They also get lost, stolen ... So, start with something like ...

enter image description here

Damir Sudarevic
  • 21,891
  • 3
  • 47
  • 71
  • Actually your answer wasn't clear but I knew the answer from your previous question :) http://stackoverflow.com/questions/1722741/defining-a-one-to-one-relationship-in-sql-server/1723519#1723519 so thanks you've helped me :) – Ahmed Magdy May 29 '12 at 20:20
1

If you really need 1:1 then both PKs would be identical and you'd have a circular FKs between them. Does you DBMS support deferred constraints, which are necessary to "break" the cycle and allow insertion of new data?

That being said, this case doesn't look like 1:1 to me. It looks like 1:N, as Damir suggested.

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167