Questions tagged [inheritance]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
19 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
29 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
37 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
51 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
655 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
95 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
248 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
266 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
199 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 ...
0
votes
1answer
64 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
364 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
1k 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 ...
1
vote
2answers
440 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
108 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
96 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
168 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
77 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
3k 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
1k 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
1k 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
338 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 ...
0
votes
2answers
116 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
899 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 ...
3
votes
1answer
3k 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
544 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
443 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
157 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
1k 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
222 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
499 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
55 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
3k 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
441 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 ...
1
vote
2answers
251 views

Dimensional Model for inheritance in dimensions

How are dimensions typically modeled in a dimensional model if one dimension logically inherits from another dimension? For example, assume I have an Employee dimension and a Driver dimension, where ...
11
votes
2answers
1k 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 ...
1
vote
0answers
262 views

Alter schema of Access 2013 database with linked table

I'm trying to change the schema of an Access 2013 table. I want to lengthen some of the text fields. When I change the schema and try to save I get dialog with Operation is not supported for this type ...
3
votes
1answer
1k views

Enhanced ER Model Supertype / Subtype modeling issue with inheritance and recursion

Before posting, I have looked for a good solution on internet and I have coined this post for 3 days in row and trying to think through it prior this post. So if I have missed something or got it ...
4
votes
2answers
987 views

How to enforce data consistency across one-to-one relationship in an inheritance hierarchy

Consider this design for a typical school database: Person: ----------------- FirstName LastName SocialSecurityNumber Phone Email Student: ----------------- Grade Teacher: ----------------- ...
0
votes
2answers
120 views

Inheritance of unknown type at creation time

Say I have a table called People into which people are inserted from the moment they are born. Now lets say I want some subclasses of people based on their job. So there are also tables for Plumber, ...
4
votes
2answers
5k 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 ) ...
3
votes
2answers
3k 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 ...
1
vote
1answer
277 views

Will switching from Concrete Table Inheritance to Single Table Inheritance improve the performance of my queries?

I currently have an application that allows people to view and share 10 different types of posts (ex SportsPost, WorldNewsPost, TechnologyPost, etc). All posts types are conceptually the same, but ...
0
votes
3answers
3k views

Can i have one foreign key to both parent and it's childs in postgresql?

I want to have a foreign key to reference table and it's children. Here's how I tried and failed: CREATE TABLE parent ( id serial, CONSTRAINT parent_pk PRIMARY KEY(id) ); CREATE TABLE child ( ...
0
votes
1answer
160 views

PostgreSQL migrate data from one table to another (which inherits the first one)

I have a table "company" and another named "school" ("school" table inherits "company"). Most of the data is community generated and sometimes a cleaning is needed. I would like to be able to "...
3
votes
1answer
676 views

Inheritance or lot of relationship?

I have to transpose a class Diagram into a Physical Data Model (more precisely MySQL database structure). In my class diagram, I have a lot of entities (A, B, C, D) which inherit from an other entity,...
10
votes
5answers
48k views

Insert into select into multiple related tables using INSERT_IDENTITY

Okay setting the scene. I have three tables, (Table1, Table2 and DataTable) and I want to insert into Table1 and Table2 using DataTable as source. So for every row in DataTable I want a row in Table1 ...
0
votes
1answer
1k views

Inheritance discriminator column better int or varchar or enum?

What is the best practice for storing a discriminator column ? Does it make any difference ? Any pros/cons of using one over the other ? I am using MySql with Entity Framework but the question goes ...
0
votes
0answers
441 views

PostgreSQL and Single Table Inheritance

I read about STI (Single Table Inheritance) and like the idea. Does PostgreSQL handle 95% NULL values in some columns if I have about 1M rows well? Does it impact performance if there many columns (~...
0
votes
1answer
465 views

Foreign key double inheritance SQLITE

I'm designing a domotic system database and I'm trying to figure out how to represent two foreign keys. This is the cardinality I want. Consider 1...N Areas (rooms, halls, kitchens, etc.): Area 1 - ...