Questions tagged [inheritance]
For questions about extending a base object's features into a derived object.
88 questions
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 ...
- 213
11
votes
5
answers
56k
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 ...
- 235
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'...
- 39.5k
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
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.
...
- 81
7
votes
1
answer
5k
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(...
- 285
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 ) ...
- 173
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, ...
- 171
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 ...
6
votes
1
answer
3k
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 ...
- 285
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 (
...
- 159
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 ...
5
votes
2
answers
1k
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.
...
- 153
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 ...
- 143
4
votes
2
answers
4k
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 ...
- 151
4
votes
2
answers
1k
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:
-----------------
...
- 1,505
4
votes
1
answer
2k
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 ...
- 143
3
votes
1
answer
4k
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 ...
3
votes
1
answer
1k
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,...
- 131
2
votes
3
answers
593
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 ...
- 21
2
votes
1
answer
382
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,
...
- 23
2
votes
2
answers
4k
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 ...
- 83
2
votes
1
answer
835
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 ...
- 123
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 ...
- 131
2
votes
1
answer
101
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 ...
- 123
2
votes
1
answer
543
views
Is this a step in the right direction for normalization? SQL 'inheritance' and Many-to-Many relationships
Context:
So, this is kind of a compound question, but they go together, and definitely wouldn't make sense split up into separate questions.
I've been working on a new database for what will end up ...
- 31
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 ...
- 198
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 ...
- 179
2
votes
1
answer
428
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 ...
- 121
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 ...
- 121
2
votes
2
answers
497
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 ...
- 751
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 ...
- 121
2
votes
0
answers
784
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 ...
- 293
2
votes
0
answers
294
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 ...
- 1,029
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 ...
- 131
1
vote
1
answer
2k
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 ...
- 307
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 ...
...
- 139
1
vote
1
answer
2k
views
Cannot add or change records
I'm designing a database structure wherein I like to apply the concept of inheritance.
There's a base tables and derived tables in which the relationship is one-to-one. I'm quite new as of how could ...
- 127
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
(
...
- 113
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 ...
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
|-> ...
- 113
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
);
...
- 321
1
vote
1
answer
1k
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,116
1
vote
1
answer
539
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 ...
- 111
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 ...
- 1,497
1
vote
0
answers
942
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/...
- 813
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 ...
- 379
1
vote
0
answers
1k
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 ...
- 111
1
vote
0
answers
284
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 ...
- 127
0
votes
3
answers
5k
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 (
...
- 257