Re: [GUSDEV] PK constraints for version tables

[<msa...@pc...>]

The problem with that is that the ID alone isn't necessarily unique-- a 
row that
has been edited multiple times will end up in the version table 
multiple times,
hence the addition of the modification_date to (attempt to) ensure uniqueness.

--Mike

Quoting Steve Fischer <sfi...@pc...>:

> yes, i guess so.
>
> steve
>
> Deborah Pinney wrote:
>
>> You mean drop the uniqueness constraint for the original 
>> PK+modification_date?
>> Steve Fischer wrote:
>>
>>> how about just dropping the uniqueness constraint of the modification date?
>>>
>>> steve
>>>
>>> Chris Sarnowski wrote:
>>>
>>>>
>>>> I can't speak for GUS needs in this particular case, but I've had 
>>>> the  same issue in other systems. Deborah is correct in that these 
>>>> fields  are not right for PK. The question is how important it is 
>>>> to preserve  exact order of the changes to the data. If it's not 
>>>> critical you  could just drop the PK. If it is (or even if it's 
>>>> not but you believe  in the rule "every table must have a PK"), 
>>>> then you should use a  surrogate key from a sequence. In practice, 
>>>> at least, the sequence  values increase monotonically so changes 
>>>> to a record that occur  multiple times with the same modification 
>>>> date can still be ordered  correctly.
>>>> I prefer the latter, in general, but I suppose adding a new 
>>>> surrogate  key field to all version tables in GUS would be a 
>>>> pretty big deal.
>>>>
>>>> Just my thoughts.
>>>> -Chris Sarnowski
>>>>
>>>> On Oct 21, 2005, at 11:07 AM, Deborah Pinney wrote:
>>>>
>>>>> I suggest that we drop the uniqueness constraint for PKs of 
>>>>> version  tables. PKs for these tables consist of the original PK 
>>>>> and the  modification date. Rapid computation can result in 
>>>>> uniqueness  copnstraint violations that are not remedied by date 
>>>>> format  changes. There doesn't seem to be a purpose in having 
>>>>> these  constraints for any of the version tables and dropping the 
>>>>>  constraints would not result in the loss of information or 
>>>>> change  query results. I don't know if there is implicit indexing 
>>>>> on these  keys but I wonder if this could be handled using 
>>>>> explicit indexing.
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> -------------------------------------------------------
>>>> This SF.Net email is sponsored by:
>>>> Power Architecture Resource Center: Free content, downloads, discussions,
>>>> and more. http://solutions.newsforge.com/ibmarch.tmpl
>>>> _______________________________________________
>>>> Gusdev-gusdev mailing list
>>>> Gus...@li...
>>>> https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev
>>>
>>>
>>>
>>>
>>>
>>> -------------------------------------------------------
>>> This SF.Net email is sponsored by:
>>> Power Architecture Resource Center: Free content, downloads, discussions,
>>> and more. http://solutions.newsforge.com/ibmarch.tmpl
>>> _______________________________________________
>>> Gusdev-gusdev mailing list
>>> Gus...@li...
>>> https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev
>>
>>
>>
>>
>>
>> -------------------------------------------------------
>> This SF.Net email is sponsored by:
>> Power Architecture Resource Center: Free content, downloads, discussions,
>> and more. http://solutions.newsforge.com/ibmarch.tmpl
>> _______________________________________________
>> Gusdev-gusdev mailing list
>> Gus...@li...
>> https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by:
> Power Architecture Resource Center: Free content, downloads, discussions,
> and more. http://solutions.newsforge.com/ibmarch.tmpl
> _______________________________________________
> Gusdev-gusdev mailing list
> Gus...@li...
> https://lists.sourceforge.net/lists/listinfo/gusdev-gusdev
>