Questions tagged [inheritance]
For questions about extending a base object's features into a derived object.
88 questions
0
votes
1
answer
17
views
Postgres permissions on table create not propagated
we are working with a postgres database V13. We're facing a problem that previously permissions were just inherited/created when a new table was created. It is not working anymore, when I create a new ...
- 56.4k
0
votes
1
answer
337
views
Can Postgres inherited tables overlap
Is it possible to have single row in a parent table be visible in more than one inherited (child) table?
Perhaps by inserting a row directly into the master table and then "making it visible" in one ...
CommunityBot
- 1
1
vote
1
answer
706
views
Django Multi Table Inheritance and Preserving Child and Child History
Place can have many different one to one fields, that’s why it can have bookstore, restaurant, and hardware store. But can the same place be both a bookstore and a restaurant at the same time? Now if ...
CommunityBot
- 1
-1
votes
1
answer
51
views
How to INSERT a new record into 2 tables with FKEY inheritance?
Inheritance has been setup with FKEY instead of INHERITS due to FKEY limitation.
My question: How to INSERT a new record into bonds table?
CREATE TABLE IF NOT EXISTS instruments (
id SERIAL,
...
- 182k
0
votes
1
answer
343
views
When using class inheritance, can all PKs of the derived tables exist as PKs of the base table?
Brief questions
When class inheritance is used, the child table inherits the key from the parent, right? Thus, you INSERT INTO the parent and then the child?
This indicates that the PK of the child ...
CommunityBot
- 1
1
vote
2
answers
453
views
Oracle MV requires object type to be defined as FINAL?
I want to create an Oracle 18c materialized view (MV) on a table that has a user-defined datatype called ST_GEOMETRY:
The MV would do a COMPLETE refresh on a schedule — on a local table.
Some info ...
CommunityBot
- 1
7
votes
2
answers
5k
views
Polymorphic Association - is it bad?
In the following schema:
Collections
Upvotes
Reports
Upvotes
Reviews
Upvotes
I'm tempted to have a single Upvotes table with a single "entityId" column that stores either the CollectionId, ...
- 14.4k
0
votes
1
answer
173
views
How to correctly deal with IDENTITY fields on parent/child tables when using inheritance in PostgreSQL
I have a rather simple question when playing with a PG 15.1 database.
I've tried to set up a simple inheritance case:
DROP TABLE IF EXISTS cities CASCADE;
CREATE TABLE IF NOT EXISTS cities (
id INT ...
- 56.4k
0
votes
0
answers
25
views
Postgresql Inheritance : Parent can be in two Child and c
In postgres sql inheritance:
Parent: Person
Child : Professor
Child : Student
Student can be a Professor
How to manage insert with solo parent ?
0
votes
1
answer
78
views
Move row of a table to an inherited table in PostgreSQL
I have tables created as such:
BEGIN;
CREATE TABLE tag (
id SERIAL,
name VARCHAR(255) UNIQUE NOT NULL,
description VARCHAR(511) NOT NULL DEFAULT '',
PRIMARY KEY(id)
)...
- 101
0
votes
1
answer
23
views
Alerting system relationships and STI in PostgreSQL
I'm no expert in DBMS and trying to build up an alerting system that can attach itself to many tables, so I figured asking the question would help my architecture.
The thing that may confuse me a ...
- 101
0
votes
1
answer
5k
views
Inheritance and foreign keys in Postgres
This is about using inheritance and foreign keys in Postgresql databases.
Consider the following simplistic example whose structure is based on what I am building at the moment (but the specifics were ...
CommunityBot
- 1
2
votes
1
answer
1k
views
Postgres natively partitioned tables - adding extra child columns
With Postgres native partitioning, can you add extra columns to a partition of the base table? Like:
CREATE TABLE item (
tenant_id bigint NOT NULL,
item_id bigint NOT NULL,
kind text NOT ...
CommunityBot
- 1
0
votes
1
answer
804
views
Correct entity relationship diagram representation of a normalized table with a NOT NULL foreign key
Intro
Hi everyone, this is mainly a classification/theoretical question on the topic of inheritance and normalization techniques in database design and their appropriate representation in entity ...
- 959
0
votes
1
answer
112
views
table inheritance: TPH, TPT and single table vs taxonomies
according to this post and many others web resources, you can do table inheritance with Table Per Hierarchy Inheritance, Table Per Type Inheritance or Single table (single table for all sub classes).
...
- 39.5k
2
votes
1
answer
78
views
Preserving foreign key properties when using Class Table Inheritance (or alternatives)
Imagine I have a table events, which outdoor_events and indoor_events inherit from. I have activities then outdoor_activities and indoor_activities. Assume it makes sense for outdoor and indoor to be ...
- 25.1k
0
votes
1
answer
135
views
Make all users within group role inherit permissions
I have a group role that consists of around 1,000 users. Those users do not inherit group role permissions, and I would like to make them all inherit them instead.
What would be the way to do it ...
- 56.4k
0
votes
1
answer
313
views
partitioning with inheritance
Reference: https://www.postgresql.org/docs/current/ddl-partitioning.html#:~:text=return%20an%20error.-,5.11.3.%C2%[…]20Using%20Inheritance,-While%20the%20built
I was trying partitioning with ...
- 56.4k
2
votes
1
answer
222
views
Designing an extensible authentication system
I need to design an authentication system, where different type of authentication schemes may exist. At the end, I should assign a passphrase (access token) to the user.
I have several authentication ...
CommunityBot
- 1
5
votes
2
answers
14k
views
Best practise for polymorphic associations in MySQL
I have a MySQL database with 3 tables holding the main classes of data:
companies (company_id)
persons (person_id, company_id)
loans (loan_id, company_id)
Both a 'loan' and a 'person' belong ...
0
votes
2
answers
159
views
Why does selecting an attribute of an Oracle object require an alias?
My GIS software has an Oracle 18c object type called ST_GEOMETRY.
The object type has attributes:
The attributes of the spatial type represent the following
information:
Entity: The type of ...
- 1,497
7
votes
2
answers
16k
views
How to drop inheritance?
I am new to PostgreSQL. The situation I have is someone created a child table inherits from the parent table. And dropped the child table. But the parent table still has the "down arrow" sign on it.
...
- 182k
7
votes
1
answer
941
views
PostgreSQL and table inheritance
I'm not sure if this is the right place for this, so please feel free to tell me if it is off-topic or there is a better place to post.
I am working on a fantasy sports based project, and the number ...
- 182k
0
votes
0
answers
891
views
How to implement concrete table inheritance pattern in MySQL?
I want to know how to implement classes in MySQL that represent a subset of another class type, with the purpose of having subclasses inherit fields from the parent class. Described by Martin Fowler ...
- 109
7
votes
2
answers
12k
views
Query parent table and get child tables columns
I have two tables, one inherits the other:
CREATE TABLE parent (
col1 INTEGER
);
CREATE TABLE child (
col2 INTEGER
) INHERITS( parent );
// Insert some data
INSERT INTO parent( col1 ) ...
- 182k
0
votes
1
answer
367
views
How can I query my "subtype" tables?
I am trying to model inheritance in my database. The purpose of the database is to use it to emulate a directory structure in a desktop GUI. The directory structure is made up of nodes, which can ...
- 123
1
vote
1
answer
223
views
Class Table Inheritance combined with running searches and paging
Assume we have tables similar to the following:
CREATE TABLE Base
(
ID int,
Type varchar(20),
Col1 varchar(255),
Col2 varchar(255),
AddedAt datetime2
);
CREATE TABLE Sub1
(
...
CommunityBot
- 1
8
votes
2
answers
11k
views
Partial unique constraint spanning multiple tables in postgres
To enforce partial uniqueness in postgres, it is a well known workaround to create a partial unique index instead of an explicit constraint, like so:
CREATE TABLE possession (
possession_id serial ...
- 251
1
vote
2
answers
2k
views
Deleting rows from child table without deleting from parent [closed]
I have 2 tables, connected with inheritance.
I want to delete some rows from the child table. The deletion part works but it deletes rows from the parent as well:
CREATE OR REPLACE FUNCTION ...
- 182k
0
votes
1
answer
84
views
Mapping and Inserting Values into a Specialized table
For a system i'm designing,i have one parent entity,Computer
Computer(computer_id,computer_processor,computer_hddrive,computer_memory);
A computer can be of two types,Desktop and Laptop,each having ...
CommunityBot
- 1
0
votes
1
answer
385
views
Modeling Table-Per-Type in PostgreSQL
I have read the following post How do you effectively model inheritance in a database? about inheritance in databases. Unfortunately I could not find anything in the PostgreSQL books from Packt. And ...
- 56.4k
0
votes
0
answers
111
views
Poor performance in a SELECT using Inheritance
I will detail my problem in good detail. I have a marketplace application so I make a decision to make a multitenant per schema approach. I like this approach because of the isolation of data, but I'm ...
- 101
0
votes
1
answer
3k
views
Postgres role does not inherit permissions from its member role
I want to group my postgres database permissions to set of roles and then assign users to these parent roles to manage all permissions from a central place. I created a role called admins and granted ...
- 21
0
votes
1
answer
369
views
Inheritence and Primary Key Constraints
I am currently trying to see how the object oriented features come in play in Postgres. During a few tests I noticed that when you insert in or delete from inheriting tables that those changes also ...
- 56.4k
1
vote
1
answer
771
views
Improve PostgreSQL SELECT performance in a parent table (inheritance)
I have a PostgreSQL 9.2 database where each account has a schema, like below:
My_Database
|-> Schemas
|-> AccountA
|-> AccountB
|-> AccountC
|-> AccountD
|-> ...
- 182k
0
votes
1
answer
180
views
PostgreSQL 10.6: Alter parent table will block ALL children?
I want to add a column to near 74.000 child tables. These tables continually have insertions and reads from all the services in the company, in almost realtime. If I add a column to the parent table, ...
CommunityBot
- 1
0
votes
0
answers
124
views
I have multiple different objects which share most attributes, but vary on a few, what methods could I use to model them?
I have a few different items which mostly share the same attributes, but then have a few extra/less. In OOP they could be modeled well with inheritance, but postgres' inheritance is limiting and doesn'...
- 111
1
vote
1
answer
447
views
Can I improve the way pg_dump exports inherited tables?
Given a postgresql 12 database with some table inheritance, and the child table adds no additional columns:
CREATE TABLE parent (
id integer NOT NULL PRIMARY KEY,
name text NOT NULL
);
...
- 56.4k
0
votes
1
answer
522
views
Table Partitioning Using Inheritance and Triggers
I am trying to do table partitioning in PostgreSQL using inheritance and triggers but the problem that have occured to me is that the data does not move from the parent table to the children tables I ...
- 56.4k
0
votes
1
answer
157
views
Pass information to another table with the same inherit
Suppose the following scenario ... I have two tables (A, B) that inherit from a Z table, the SGBD is postgresql, these tables do not have an additional column, my question is how to pass a row from ...
- 36
8
votes
1
answer
10k
views
What is the purpose of "Schema Owner"?
If I create a new schema and set the schema owner to another schema, for example dbo, what is the meaning of that in regards to my new schema?
Does my new schema inherit the same permissions from it'...
- 47.6k
0
votes
1
answer
1k
views
PostgreSQL Trigger to override insert on table with inheritance
We have a table documents that is partitioned by time into child tables like documents_2019_01, documents_2019_02. We do this using a BEFORE trigger on the parent documents table to insert into the ...
- 3,601
0
votes
1
answer
429
views
Progressive child tables scan in PostgreSQL?
We have append-only events table where events from different devices are being collected.
We use PostgreSQL 9.6 and pg_partman to split the table into monthly partitions.
Data is partitioned using ...
CommunityBot
- 1
2
votes
2
answers
545
views
DB model strategy to override field value
We have to store some dynamic configuration in JSON format in DB. There would be some default configuration, and it can be overriden per account. Can someone help me with the strategy to store such ...
CommunityBot
- 1
0
votes
1
answer
137
views
make exist table as inherit
i try to create parent table for exists table, by next algorithm:
-- CREATE TABLE
CREATE TABLE t1 (id serial PRIMARY KEY);
CREATE TABLE foreign_t1 (id serial, t1_id integer REFERENCES t1 (id));
-- ...
- 41.3k
1
vote
2
answers
1k
views
Views that inherit a table and syntax in PostgreSQL
I am getting a syntax error but don't understand what is wrong.
I am using a reference here.
This is my sql-snippet:
CREATE OR REPLACE VIEW stipa_test.stipa_view
AS
(
SELECT *
FROM ...
...
CommunityBot
- 1
2
votes
0
answers
274
views
Is it possible to drop inheritance from the parent table (top down)?
Our system uses inheritance on two levels:
master
child_level_1
child_level_2
child_level_2
child_level_2
child_level_1
child_level_2
child_level_2
child_level_1
child_level_2
The idea was to ...
- 98.6k
0
votes
1
answer
680
views
Altering a parent table in Postgresql 8.4 breaks child table defaults
The problem: In Postgresql, if table temp_person_two inherits fromtemp_person, default column values on the child table are ignored if the parent table is altered.
How to replicate:
First, create ...
- 64.6k
11
votes
2
answers
3k
views
Conceptual ERD Multi-table many to many, or possibly recursive?
I'm creating a conceptual diagram [yes, I know I've included attributes and keys - but this is just for me to consolidate what I'm doing whilst learning] -- so please treat it as Conceptual with the ...
CommunityBot
- 1
5
votes
1
answer
3k
views
Transfer row from 'parent' to 'child' without creating duplicate and without knowing all 'parent' columns
I want to set up a database with different schemas that correspond to different data-entry systems. There needs to be a “master” (administration) schema with a “parties” table, and multiple other ...
- 64.6k