sql server 2008 - SQL table design with contigent constraints -

-

i have table design problem need cunning solution.

let's have 2 tables, relationship:

contract 1---n payment

now, let's have legacy data needs go these tables. problem though many of legacy payment entries aggregated across mulitple contracts

so view as:

new:

somethingabovecontract 1---n contract 1---n payment

legacy:

somethingabovecontract 1---n payment

now, can around creating m-n relationship between contract , payment. 

contract 1---n contractpayment n---1 payment

(it possible me identify contracts linked aggregated payment)

this fine legacy data, want enforce 1-n relationship between contract , payment going forward. so, using unhandy scribble illustrate, this:

enter image description here

i.e. payment aggregate, contractid null, otherwise should not null. in other words, need find way enforce following contingencies on payment table:

  1. contractid nullable if paymentid appears in contractpayment
  2. contractid not nullable if paymentid not appear in contractpayment

i don't know how though.

even if possible, seem bit ugly (which legacy data conversion invariably is). if has more elegant solution great. otherwise, works!

thanks
karl

use 2 (sets of) tables, 1 'legacy' , 1 going forward. should able define business rules , without need nullable columns (sql's 3 value logic disaster). privileges revoked on 'legacy' table(s) ensure not used going forward.


Comments

Post a Comment

Popular posts from this blog

c# - how to write client side events functions for the combobox items -

-
<dx:aspxcombobox id="aspxcombobox2" runat="server" clientidmode="autoid" selectedindex="-1" onselectedindexchanged="aspxcombobox1_selectedindexchanged1" > <clientsideevents selectedindexchanged="function(s, e) { grid.selectrows();}" /> <items> <dx:listedititem text="selecte rows" /> <dx:listedititem text="unselectall" /> </items> </dx:aspxcombobox> i want write event "unselect all" item in combobox. how can this? you'll need write javascript function iterates thru combo-box , marks selected items false. see select/unselect checkbox fields using pure javascript , html sample code msdn:
Read more

exception - Python, pyPdf OCR error: pyPdf.utils.PdfReadError: EOF marker not found -

-
pypdf throws exception: pypdf.utils.pdfreaderror: eof marker not found i don't need fix pypdf, need eof error cause "except" block execute , skip on file, doesn't work. still causes program stop running. background: batch ocr program pdfs python, pypdf, adobe pdf ocr error: unsupported filter /lzwdecode ... saga continues. i got 10,000 pdfs in folder. ocrd, not. can't tell 'em apart. step 1 figure out ones not ocrd , ocr (see other threads details). so i'm using pypdf. exceptions related unrecognized characters , unsupported filters when try read text. guestimated if throws exception, it's got text in , doens't go in list. problem solved, right? so: pypdf import pdffilewriter, pdffilereader import sys, os, pypdf, re path = 'c:\users\homer\documents\my pdfs' filelist = os.listdir(path) has_text_list = [] does_not_have_text_list = [] pdf_name in filelist: pdf_file
Read more