Search code examples
sql-serversql-server-2008entity-framework-4.1entity-relationshiprelation

Implementing one-to-one relation in SQL Server with two principle table

I have these three tables:

1:

enter image description here

2:

enter image description here

Sell and Cheque are in dependent upon FinancialTransaction. As you see in the first picture, I have put FinancialTransactionId in dependent tables but I'm not sure that it is correct because as far as I know, One should put FK in the dependent table. In the other way if I put ByeId and ChequeId in the FinancialTransaction(Picture 2),one of them is always empty,so I don't know which method is right? And in addition the principle tables could be more than two.

Solution

  • The first approach is better and seems that it has no drawback.

    But if you want to maintain the dependency in that order only, then a better approach could be to combine the cheque and sell table if they both have the same columns and add another boolean column IsCheque which will be set true or false depending on the payment mode.

    EDIT:

    Other approach could be to take a boolean column IsPaymenyCheque and another column as PayModeID. Based on the IsPaymenyCheque, refer to either of the table with PayModeID.

    This would complex your queries as you will have to check for the Boolean value each time. Also you will have to remove the foreign key constraint.