Re: looking for a globally unique row ID

On 09/14/2017 02:39 PM, Rafal Pietrak wrote:
>
> W dniu 14.09.2017 o 19:30, Rob Sargent pisze:
>>
>> On 09/14/2017 11:11 AM, Rafal Pietrak wrote:
> [------------------]
>>> So I'm stuck with seriously incomplete solution.
>>>
>>> that's why I have an impression, that I'm going into entirely wrong
>>> direction here.
>>>
>>>
>> So you care if the same id is used in separate, unrelated tables? What's
>> your fear here? And I completely get the confusion generated be the
> You can call it fear, but I see it as an attempt for "schema/design
> resilience".
>
> Like with "unique constraint". You lay out the schema, and whatever bug
> get planted into an application, the database does not let you put
> inconsistent data there.
>
> And answering your question, my problem is actually trivial. I need that
> to systematically cover document identification within archive. There
> are couple of document "Classes" I need to cover, their representation
> in the DB requires different columns, so they are registered in
> different table. I've started the project with one table for all those
> classes, and quite soon it become monstrous.
>
> So every document class got their own table. (and their private unique ID).
>
> But problem rises when I need to referencing those documents along their
> lifetime. Every class needs separate table for that. I cannot have a
> single table of "history" (document-ID, dispatch-date, recepient), as
> I'm not able to make FK from single table into multitude of tables (with
> separate classes). So the forest of tables grows.
>
> The above schema would get significantly simpler if only I could have a
> "global primary key"... or something equivalent.
>
> And as I said, this is not a problem of performance (or identification
> space exhaustion). It's a problem of "schema explosion". Something that
> actually is quite simple become complex (if not unmanageable) just
> because I cannot see a good tool to manage the "globality".
>
> I kick this problem for some time now - rewriting the basic schema
> numerous times, and:
>
> 1. either I get monstrous "document master table" - which very
> effectively leads to contents inconsistency (document get attributs
> which dont belong to their classes)
>
> 2. or the schema explodes, as with documents stored in smaller (less
> columns) specialized by-class table ... they all need their private
> "managemnet tables" which FK into its respective document tables.
>
> Both ways turned out to be quite expensive in maintenance.
>
> Throwing actual numbers: 12 basic classes of documents; 17 tables
> registering various operations document may undergo during its lifetime.
> Variant (2) above make it 12*17 = 204 tables, which I'm currently
> maintaining.... and it's too much. With variant (1) I simply wasn't able
> to effectively keep document attributes consistent.
>
> Thus I'm searching for tools (paradigms/sql-idioms) that would fit the
> problem.

Isn't this typically handled with an inheritance (parent-children)
setup. MasterDocument has id, subtype and any common columns (create
date etc) then dependents use the same id from master to complete the
data for a given type. This is really common in ORM tools. Not clear
from the description if the operations could be similarly handled
(operation id, operation type as master of 17 dependent
operationSpecifics; there is also the "Activity Model")
>> same small integer being re-used in various context ("sample id" is the
>> bane for me). Could you use a sufficiently accurate time value?
>>
>>
>>
>
> But thank you all for answers.
>
> -R
>
>