Re: looking for a globally unique row ID

W dniu 16.09.2017 o 22:20, rob stone pisze:
>
>
> On Sat, 2017-09-16 at 19:18 +0200, Rafal Pietrak wrote:
>> Dear robjsargent(at)gmail(dot)com,
[-------------]
>>
>>
> Hello Rafal,

Hi,

>
>
> I've been trying to follow this discussion but now I'm totally
> confused. (Some people might say that this is my normal state.)
>
> However, what do you mean by the following:-
>
> 17 "process tables"?
>
> multiplicated -- does this mean replicated?

To pinpoint the schema design I'm reducing the example complexity just
to one level of hierarchy in "class-A", and just to one table in
"class-B". It goes like this:

CREATE TABLE T1 (id int, tm timestamp)
CREATE TABLE T2 (me int, him int) INHERITS (T1) -- level 1
CREATE TABLE T3 (me int, him int) INHERITS (T1)

ALTER TABLE T2 ADD CONSTRAINT t1_pk PRIMARY KEY(me,id)
ALTER TABLe T3 ACC CONSTRAINT t1_pk PRIMARY KEY(him,id)

the "logic" of processing requires here just one class-B table:
CREATE TABLE BP (id int references t1(id), info text)

but i have to create two tables instead:
CREATE TABLE BP2 (id int, me int, him int, info text, FOREIGN KEY
(id,me) references t2(id,me));
CREATE TABLE BP2 (id int, me int, him int, info text, FOREIGN KEY
(id,him) references t3(id,him));

That's the multiplication.

Every class-B table has to have the same inheritance structure as
class-A tables (frankly, not all but actually only the most, but that's
not really important).

>
> any of the 12 leaf tables I'm using -- what is a "leaf" table?
>
> collapse some of the hierarchy at the expense of some columns getting
> NULL for certain rows -- does this mean if you have two input fields
> (field A and field B) that if field A is not null and field B is null
> the data is inserted into one table and if it's the inverse you insert
> into an entirely different table?

I had this exact impression some time ago, so I've rewritten my schema
to "flat table". This didn't work. I've lost some of the constraints I
use to keep the data in order.

So I've rewritten the schema back to the inheritance hierarchy. And it
wasn't just a rollback, as the application lived with the new layout for
something over half a year and new things got implemented along the road.

Both rewrites costed me like 2 month *intense* work each. I've learned,
that current layout suits its usage best. The "features not covered
properly" are now reduced to just this single "global index problem".
And this problem is just "slower development" - I'm reluctant to put new
features, because the schema is so unnecessarily complex now. Well, be it.

>
>
>
> IMHO, you need an UML diagram that not only sets out your workflow but
> will also provide the basis for your schema.
> Keep in mind that a hierarchy can be 'n' tables deep. The foreign keys
> point back upwards until you finally reach the parent.

No, really, pls don't.

I've spend A LOT of time figuring out (and numerous times taking the
wrong turns) of the optimal schema.

This is not helping.

>
> You mention payments being made. Users make mistakes. They can post a
> payment to the wrong account and later it has to be reversed. These
> things can be modelled via your UML diagram.
>

This kind of events just go to history table.

Meaning FK in payment table get updated, while the old value is
log-registered in history table ... that log-table does not have any FK
constraints, as it's ment to be just an audit trail.

Really. All this works just fine.

-R