Re: Normalization question

On 26 Jun 2002 11:51:26 -0700, leebertarian_at_yahoo.com (leebert) wrote:
>The questions I have are:
>
>1. Is begin_dt + end_dt (incl. trigger) more complex than necessary in
>the new model?

Yes.

>2. Are the null end_dt values going to end up really hurting
>performance of the new model? Esp. if for query simplicity someone
>uses the COALESCE() function to replace the end_dt nulls with the
>current date via getdate()/current date system functions?

Not important (he said cavalierly).

What is important is that you can't include nullable fields in PKs.

>3. Is the elimination of duplicates worth the increase in complexity
>brought by the new model?

I don't see what's wrong with the old model, apparently you didn't define a unique or primary key on it.

>4. Is there a better way to model this?

For the active record, put in a flag field, a bool or a char or whatever, instead of leaving the end date null.

>Those three questions don't really address some of other problems in
>the old data model though, like when 2 records for the same year and
>month needed to exist for the same "ID", functionally creating
>duplicate keys, result being that the budget analyst had to due some
>weird gyrations by imputing the 'correct' record through reading other
>data flags. In essence, the domain of the key changed depending upon
>the context of the type of query ... Agggh! Uniqueness = The whole
>record!

I have no idea what this means.

>I don't know if I'd exactly describe this as a 2nd vs. 3rd NF issue,
>but just the fact they are conceding it *could* impact performance on
>a lesser/smaller machine, I think they might as well concede it'd
>impact a heavily used machine with multiple application databases
>(we're talking about Dell rack-mount 4-way blades FDDI'd to a SAN).

Has performance been a problem? Usually there are about fifty reasons limiting transaction performance on complete systems, efficiency of queries is usually pretty far down the list ... unless something is seriously bad in the data model or queries.

>I know if I went back to 1st principles and read some weighty tome on
>normalization I'd find an answer, but I'm taking a shot & see what
>more experienced folks have to say, save some time narrowing down
>what's best practice. My general research on this however has lead me
>to believe that using date range as part of a functional key would
>work best.

These are not really normalization questions. There are whole classes of "semantic" questions and other design issues, including some that refer purely to style, that also come up, and that's more the kind of thing I think you're asking about.

Joshua Stern Received on Fri Jun 28 2002 - 01:43:51 CEST