Re: candidate keys in abstract parent relations

I totally agree with your post there, cool! But it leads me to a question that I'd like to throw out in the airways...

We can (and do) build logic in to protect ourselves to an extent, for example make sure a date of birth is realistic; somebody isn't claiming a pension where they are only 16 etc...

That leads me to the question though, those type of data validation are more business logic aren't they or perhaps not? To what extent do we defend the validity of the data in the database, for instance an email address, in SQL Server you can now create a column say is_valid_email which can call a CLR scalar function that can run a bit of C# / VB.NET to actually go out and validate if that email address is truely valid at the point you want it...

select *
from member_list
where is_valid_email = 'Y'

I'd be interested to know peoples thoughts on this, do you keep this logic centrally within the database so that you don't have to duplicate it on each application that uses the database??

Tony.

-- 
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials


"Roy Hann" <specially_at_processed.almost.meat> wrote in message 
news:4_WdnYLUwYHrAknenZ2dnUVZ8qWdnZ2d_at_pipex.net...


> "paul c" <toledobythesea_at_oohay.ac> wrote in message


> news:ryeAf.295927$2k.205270_at_pd7tw1no...

>

>> Sometimes I think modellers go too far trying to ascribe dbms attributes

>> to the natural world.  Certainly big biz and governments do.

>

> Spot on!!

>

> When we record a driver's licence number or an employee number, and name,

> all we are saying is that we believe that number was assigned to a person

> who will claim to have that name.  Most of the time that also happens to

> work as identification for the person, but IMO that is an accidental

> side-effect of most people being honest.  But I have a colleague who works

> on a criminal justice system, and they tie themselves in fantastic knots

> because they refuse to understand this.

>

> I have never met a database designer yet, including myself, who doesn't

> struggle to remember that the database only has to assert what we are 

> told,

> NOT what is objectively true.  It doesn't "matter" if the database 

> contains

> lies as long as it (and the application) doesn't invent or derive new 

> lies.

> (Obviously there is a burden on the person entering the data to be 

> diligent

> about establishing the truth as best they can before they enter it, but

> that's not a database design problem.  Also obviously, it is nice to test

> the internal consistency of the assertions we record, if we can.  And just

> as obviously there has to be a way to remove lies and all their false

> derivations when they are discovered.)

>

> I am in danger of turning the conversation full-circle here by remarking 

> on

> how surrogate keys are helpful, so I will stop now.  (The problem IMO is 

> how

> to discourage using them, not the opposite!)

>

> Roy

>

>