Questions tagged [inheritance]
For questions about extending a base object's features into a derived object.
74
questions
1
vote
2answers
42 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 ...
0
votes
2answers
31 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
vote
2answers
139 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 ...
0
votes
0answers
27 views
How to model inheritance in MySQL for adding and deleting users?
I currently have a MySQL database and our ERD has inheritance for various tables. One is User which has as children: administrators, students and professors. The tables look as follow:
CREATE TABLE `...
0
votes
1answer
53 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 ...
0
votes
1answer
25 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 ...
0
votes
0answers
29 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 ...
0
votes
1answer
282 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 ...
0
votes
1answer
59 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 ...
1
vote
1answer
134 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
|-> ...
0
votes
1answer
25 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, ...
0
votes
0answers
26 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'...
1
vote
1answer
149 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
);
...
0
votes
1answer
32 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 ...
0
votes
1answer
15 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 ...
3
votes
1answer
2k 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'...
0
votes
1answer
431 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 ...
0
votes
1answer
173 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 ...
0
votes
1answer
41 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));
-- ...
2
votes
0answers
71 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 ...
5
votes
1answer
2k 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 ...
2
votes
1answer
142 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 ...
1
vote
0answers
542 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/...
0
votes
1answer
633 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....
2
votes
0answers
449 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 ...
1
vote
1answer
269 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 ...
2
votes
1answer
650 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 ...
0
votes
1answer
3k 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 ...
1
vote
2answers
794 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 ...
...
2
votes
0answers
175 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 ...
2
votes
1answer
142 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 ...
2
votes
1answer
236 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,
...
0
votes
1answer
144 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 ...
5
votes
3answers
6k 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 (
...
0
votes
1answer
3k 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 ...
4
votes
2answers
2k 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 ...
0
votes
1answer
466 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 ...
1
vote
2answers
273 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 ...
2
votes
2answers
2k views
Postgres inherit from one role and not another
I'm a little new with postgres but have a decent understanding of roles. What I have are users that inherit from their normal "data_analyst" group. This group is used to manage all data analyst users ...
4
votes
1answer
7k 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 ...
5
votes
2answers
776 views
Postgresql inheritance based database design
I'm developing a simple babysitter application that has 2 types of users: a 'Parent' and the 'Babysitter'. I'm using postgresql as my database but I'm having trouble working out my database design.
...
0
votes
2answers
769 views
Modeling a product database and dealing with inheritance
So, to get better at designing databases, I'm attempting to model a database system after the AR15 Builder.
Here is what I have so far:
While it makes sense logically, I'm afraid there may be ...
0
votes
1answer
261 views
Is it possible to set order in which inherited tables a scanned in postgresql?
I have a postgresql measurement table which is partitioned based on logdate column by month. Suppose I have city_id integer column and want to select latest row with city_id = 125.
When I run explain ...
2
votes
1answer
2k views
Postgres 9.5 foreign table inheritance not using indexes
In PostgreSQL 9.5.0 I have a partitioned table that collects data by months. I tried to use the new PostgreSQL feature of foreign table inheritance and pushed one month of data to another PostgreSQL ...
2
votes
3answers
396 views
Record inheritance in mySQL
I have been struggling with this for a little while.
I want to have a set of calendar records. The top level will apply to a large group; the next level will apply to a sub-set and the final level ...
1
vote
0answers
824 views
PostgreSQL doesn't use index on inherited tables
I have two tables in PostgreSQL 9.4.5 deployed on Amazon RDS:
an append only, weekly partitioned, All Positions table. These store all position readings from about 100,000 sensors. There are about 50 ...
2
votes
1answer
63 views
Finding a good DB design for my project
I've posted this on stackOverflow but this might be a more suitable place.
I'm really struggling to find a good DB design for my project using SQL server.
I've already implemented a few models which ...
6
votes
1answer
2k views
Index is not used with table inheritance
I have a PostgreSQL 9.0.12 database with a master table and 2 child tables. My tables:
CREATE TABLE test2 (
id serial PRIMARY KEY,
coll character varying(15),
ts timestamp without time ...
7
votes
1answer
4k views
Performance issues with inherited tables and indices
I have a PostgreSQL database with a master table and 2 child tables.
My master table:
CREATE TABLE test (
id serial PRIMARY KEY,
date timestamp without time zone
);
CREATE INDEX ON test(...
6
votes
1answer
582 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 ...
