Re: [GUSDEV] PK constraints for version tables

[Angel P. <an...@ma...>]

Debbie's point is that uniqueness in version tables doesn't really 
matter and it is getting in the way of fast inserts. The only ways to 
fix this are:

1) drop the constraints
2) increase the precision of row_modification_date to milliseconds.

-angel

msa...@pc... wrote:

>
>
> 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
>>
>
>
>
>
>
> -------------------------------------------------------
> 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



-- 

Angel Pizarro
Director, Bioinformatics Facility
Institute for Translational Medicine and Therapeutics
University of Pennsylvania
806 BRB II/III
421 Curie Blvd.
Philadelphia, PA 19104-6160

P: 215-573-3736
F: 215-573-9004