CODEWITHSUNDEEP
sqlsql-server-2008many-to-manydatabase-design
Karl
Karl

Reputation: 5822

SQL table design with contigent constraints

I have a table design problem for which I need a cunning solution.

Let's say I have two tables, with relationship:

Contract 1---N Payment

Now, let's say I have legacy data that needs to go into these tables. The problem though is that many of the legacy Payment entries are aggregated across mulitple contracts

So we could actually view this as:

New:

SomethingAboveContract 1---N Contract 1---N Payment

Legacy:

SomethingAboveContract 1---N Payment

Now, I can get around this by creating an M-N relationship between Contract and Payment. 

Contract 1---N ContractPayment N---1 Payment

(it will be possible for me to identify all the contracts that are linked to the aggregated payment)

This is fine for the legacy data, but I actually do want to enforce the 1-N relationship between Contract and Payment going forward. So, using my very unhandy scribble to illustrate, I would like to do this:

enter image description here

I.e. where the payment is aggregate, ContractID will be NULL, otherwise it should not be null. In other words, I need to find a way to enforce the following contingencies on the Payment table:

  1. ContractID nullable if PaymentID appears in ContractPayment
  2. ContractID not nullable if PaymentID does not appear in ContractPayment

I don't know how to do this though.

Even if this is possible, it does seem a bit ugly (which legacy data conversion invariably is). So if anyone has a more elegant solution that would be great. Otherwise, anything that works!

Thanks
Karl

Upvotes: 0

Views: 76

Answers (1)

onedaywhen
onedaywhen

Reputation: 57023

Use two (sets of) tables, one for 'legacy' and one going forward. You should be able to define the business rules simply and without the need for nullable columns (SQL's three value logic is a disaster). Privileges could be revoked on the 'legacy' table(s) to help ensure they are not used going forward.

Upvotes: 3

Related Questions