Check constraint that is a field has a particular value ensure that another field must be filled in.

-
DB time!

So this post regards "normalisation", or lack of, permitting me to be more lazy, and have simpler databases - what's not to like?

I had a DB table "users", and two distinct classes of user:  Adult and Child.  Now the business logic demands that the Adult users have an email address entered, whereas the child user doesn't necessarily need one.

The Boyce-Cod or whatever blah blah method would be to have 2 tables, but that means loads of JOINs, the inability to view the entire user list in one place, and more logic everywhere.

I'd prefer to have 1 table "Users", but how to enforce the business logic above?

So I've discovered a way - using CHECK CONSTRAINTS in MS SQL 2012+




The "redmouse" is a child user, it is optional whether he has a email address or not.



([role]='Adult' AND [emailaddress] IS NOT NULL OR ([role]='Child' AND [emailaddress] IS NOT NULL OR [role]='Child' AND [emailaddress] IS NULL))

Allows me to denormalise to my hearts content and yet retain the rock solid data stability that we all crave.

Similarly above the "childDoB" column is only relevant to "child" users.   For fun what would be the CHECK CONSTRAINT for that rule?





Comments

Post a Comment

Popular posts from this blog

How do you fix a roll of aluminium foil once it's started spiralling?

-
I had this problem. Seen a post on Reddit .  Old one. " This thread is archived New comments cannot be posted and votes cannot be cast" That is a shame.  As I now have a better answer than any of the posts. Get a pair of tweezers and scrape down from the part of the foil roll that is "stuck".   Don't matter if it digs down into the layer beneath.  Try to maintain even digging across the roll. Worked a treat!
Read more

Why does Premiere Pro "Sequence from clip" on MP4 and then "Match Sequence" result in a massive AVI file?

-
Image
Good Question! You would imagine that just "trimming" an MP4 file with same setting would be quick and painless, but when I tried it the resultant file was massive - like 10x the size of the original file. https://community.adobe.com/t5/premiere-pro-discussions/match-sequence-settings-mp4-to-mpeg-is-this-normal/td-p/11042944 Turns out this is because the .mp4 compression means there aren't really full frames in the file just deltas. So when you select "Match Sequence Settings", it has to give you AVI format anyway. So this is what you HAVE to do.   Go ahead and create your HUGE AVI file.  Then use an app or an online service to compress it back down... I had a good result using  https://cloudconvert.com/avi-to-mp4 It shrunk the 76Mb AVI to less than half a Mb.  
Read more

Churchill's Reaction to Eisenhower's Speech at the English Speaking Union 1951

-
Image
I'm still reading Never Despair Winston S. Churchill  1945-1965 by Martin Gilbert.  It is a bible-sized book, albeit the print is slightly bigger, and it will take me a while yet. Recently I've come across an interesting chapter.   This section of interesting stuff starts on Page 619. Interesting to me, and those Brits and others on the EU side of the Brexit-argument, I've found more compelling evidence to answer the question:  " Would Winston Churchill have been in favour of Britain being in the EU?" .   Of course, I believe the answer (certainly after the War) was that he would have been in favour. Ike Eisenhower made a speech at the English Speaking Union Dinner on July 3, 1951.  Churchill was there.  Churchill didn't actually hear the speech well enough at the time, but read it's transcript the following day.  He was ecstatic when he did. Eisenhower and Churchill The relevant text, of the above mentioned book, is as follows;  " ...So pleased wa
Read more