Skip to main content

Questions tagged [inheritance]

For questions about extending a base object's features into a derived object.

Filter by
Sorted by
Tagged with
0 votes
1 answer
45 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 ...
s.k's user avatar
  • 362
0 votes
0 answers
14 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 ?
KstorTroy's user avatar
0 votes
1 answer
49 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) )...
SK19's user avatar
  • 101
0 votes
1 answer
18 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 ...
Laurent's user avatar
  • 101
0 votes
1 answer
447 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 ...
papshmeare's user avatar
0 votes
1 answer
91 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). ...
Giacomo M's user avatar
  • 163
2 votes
1 answer
64 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 ...
thshea's user avatar
  • 198
0 votes
1 answer
114 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 ...
TimesAndPlaces's user avatar
0 votes
1 answer
228 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 ...
channa's user avatar
  • 350
0 votes
0 answers
823 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 ...
ike5's user avatar
  • 109
0 votes
1 answer
310 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 ...
Robert W's user avatar
  • 113
1 vote
1 answer
193 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 ( ...
deadwards's user avatar
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 ...
Joe's user avatar
  • 179
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 ...
Dionisis Kotzaitsis's user avatar
0 votes
2 answers
142 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 ...
User1974's user avatar
  • 1,457
1 vote
2 answers
400 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 ...
User1974's user avatar
  • 1,457
0 votes
1 answer
334 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 ...
Sven M.'s user avatar
  • 123
0 votes
1 answer
325 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 ...
Marion's user avatar
  • 1
0 votes
0 answers
96 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 ...
wavrik's user avatar
  • 101
0 votes
1 answer
2k 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 ...
Dilunika's user avatar
0 votes
1 answer
323 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 ...
SweetPotato's user avatar
1 vote
1 answer
738 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 |-> ...
Marcio Mazzucato's user avatar
0 votes
1 answer
167 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, ...
Cristóbal Ramos Merino's user avatar
0 votes
0 answers
82 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'...
Levi H's user avatar
  • 111
1 vote
1 answer
400 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 ); ...
James Healy's user avatar
0 votes
1 answer
454 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 ...
npateras's user avatar
0 votes
1 answer
148 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 ...
Roberto Dominguez Cazarin's user avatar
7 votes
1 answer
9k 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'...
J.D.'s user avatar
  • 37.5k
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 ...
austin_ce's user avatar
  • 115
0 votes
1 answer
399 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 ...
Konstantin Suvorov's user avatar
0 votes
1 answer
119 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)); -- ...
user avatar
2 votes
0 answers
237 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 ...
Jesse's user avatar
  • 121
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 ...
user2437443's user avatar
2 votes
1 answer
384 views

Correct way to join row in parent table to row in one of several children tables

I have a parent table product, and several children tables like phone, tablet, smartwatch etc. The parent and children have 1-1 relationship. This means that for every row in parent table, there will ...
Abdul Wasae's user avatar
1 vote
0 answers
926 views

Implementing a graph-like design on postgresql using CTEs and inheritance

This question is about attempting to model/design an elementary graph-like database within postgres. The starting point is that graphs have: Nodes Nodes have properties Node are connected by (bi/...
ahron's user avatar
  • 803
0 votes
1 answer
3k views

How to list all the inherited tables from the parent table?

I have 3 tables (janitor, security & manager) which inherited from the user table. What is the query can be used to produce the following result(list all the name of the child tables)? Thank you....
TYL's user avatar
  • 3
2 votes
0 answers
770 views

Is it correct to model a specialized table using a foreign key whose referencing attributes are not also forming its primary key?

In the process of designing a database model, I would like to implement a Class Table Inheritance pattern in a way that does not seem common at first. I would like to know if it is a correct ...
soliz's user avatar
  • 293
1 vote
1 answer
941 views

user security inheritance in SQL Server 2016

I'd like to build a bunch of new databases (several dozen) in SQL Server 2016. However, I'd like all users to access a single database (called SECURITY for now), and apply security to that one ...
JustBeingHelpful's user avatar
2 votes
1 answer
821 views

Database relationship inheritance [closed]

I have a vehicle sales store. In my store I sale many kind of vehicles for example cars and boats. And I would like to keep track of the sales. My vehicles has some attributes as: Arrive on store ...
Lin's user avatar
  • 123
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 ...
ahron's user avatar
  • 803
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 ... ...
Michael's user avatar
  • 139
2 votes
0 answers
283 views

PostgreSQL database re-design considerations

We have a 2TB PostgreSQL database, which contains two groups of tables that account for about 90% of the storage used. These two groups can be loosely described as 2 big tables partitioned, the first ...
Gonzalo Vasquez's user avatar
2 votes
1 answer
218 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 ...
Buğra Ekuklu's user avatar
2 votes
1 answer
366 views

Inheritance of "dictionary tables"

(In case this matters: I'm using SQLite) My silly little database has a surprising number of tables with identical structure: CREATE TABLE GTag ( ID INTEGER PRIMARY KEY NOT NULL, ...
Vestin's user avatar
  • 23
0 votes
1 answer
323 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 ...
Johan's user avatar
  • 613
5 votes
3 answers
9k views

Using table inheritance instead of mapping tables

This seems like a pretty common scenario: several types that all compose the same child type. This could typically look like so: -- 'name' is unique per parent record CREATE TABLE sometype ( ...
Dmitri's user avatar
  • 159
1 vote
1 answer
5k views

PostgreSQL: Multiple Inheritance tables (one child to two parents)

If I have a base table and 2 or more tables that inherit from it, child1 and child2, I can insert a record into a child table and it shows up in base as you would expect. However what I want to know ...
FireLeopard's user avatar
4 votes
2 answers
3k views

Abstract classes in SQL Server. Are they even possible?

I have a very specific question that I am surprised no one has yet asked. It involves a concrete class model and SQL Server 2016 DDL. The main point is: In my model, some classes inherit from a base ...
Petros Apotsos's user avatar
1 vote
1 answer
662 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 ...
Malik A. Rumi's user avatar
2 votes
2 answers
511 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 ...
sidgate's user avatar
  • 131