W dniu 15.09.2017 o 13:36, Alban Hertroys pisze:
> On 15 September 2017 at 11:03, Rafal Pietrak <rafal@ztk-rp.eu> wrote:
>
[-----------------]
>
> For example, if we define:
> create table master_table (
> year int
> , month int
> , example text
> , primary key (year, month)
> );
>
> create child2016_table () inherits master_table;
>
> alter table child_table add constraint child2016_year_chk check (year = 2016);
> alter table child_table add constraint child2016_pk primary key (year, month);
>
> create child2017_table () inherits master_table;
>
> alter table child_table add constraint child2017_year_chk check (year = 2017);
> alter table child_table add constraint child2017_pk primary key (year, month);
>
> In above, the three separate primary keys are guaranteed to contain
> distinct ranges of year - provided that we forbid any records to go
> directly into the master table or that those records do not have years
> already covered by one of the child tables.
>
> Perhaps you can apply this concept to your problem?
>
I do it exactly this way.
The problem is, that the documents undergo "postprocessing" - 17 other
tables "would" describe those .... and MUST keep track of what's done
and to which document, which is done by FK into relevant document table.
Having this partitioning, instead of having those 17 "process-tables" I
have to create 17 * 12 = 204 tables to be able to implement those FK (ID
+ child-selector); while if only I could avoid that "child-selector"
(like YEAR in your example), it would let me reduce my schema like
10-fold (hmm, not exactly 12+17 = 29 tables v.s. 204 tables, but close
enough).
The complexity of this design have already made me stop adding
functionality ... so I'm looking for means of reducing it. An obvious
candidate would be something like "global primary key" over all the
partitions of the master document table.
But I understand, no such thing exists.
Thenx anyway,
-R
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general