14
votes
Accepted
Conceptual ERD Multi-table many to many, or possibly recursive?
It is great that you are taking the time to understand, classify and model the data you are dealing with since, from my personal experiencie, all this makes the whole development process easier and ...
- 8,370
12
votes
Accepted
Performance issues with inherited tables and indices
"date"
Don't call your timestamp column "date", that's very misleading. Better yet, don't use the basic type name "date" as identifier at all, that's error-prone, leads to confusing error messages ...
- 167k
9
votes
Using table inheritance instead of mapping tables
Inheritance is one of those features that I wouldn't touch. AFAIK, it's used internally for replication and partitioning in some capacity. I'm not sure if it was even designed with the intent to be ...
- 60.4k
9
votes
Accepted
What is the purpose of "Schema Owner"?
A Schema's owner is always a User (or Database Role). There may be a Schema with the same name as a User, because in very old versions of SQL Server a User and a Schema were the same thing. So there'...
- 44.3k
8
votes
Accepted
Query parent table and get child tables columns
As long as the set of inherited column values is unique across all tables, there is a simple solution with a NATURAL join (one of the rare use cases for this clause!):
SELECT * FROM ONLY parent ...
- 167k
8
votes
Accepted
Best practise for polymorphic associations in MySQL
This question (in various forms) crops up regularly on this forum.
It is called EAV (Entity Attribute Value). For good reason, it is called an antipattern. There are many reasons not to use this form ...
- 28.9k
6
votes
Using table inheritance instead of mapping tables
The mentioned deficiencies are not a reason not to use inheritance!
Inheritance works here similar to Class-Inheritance with independent Objects.
You may have a class/table 'fruits' and a class/table '...
6
votes
Accepted
Postgres 9.5 foreign table inheritance not using indexes
Postgres can use indexes on the foreign server. But there are quite a few more obstacles than for local tables. Read the chapter Remote Query Optimization in the manual.
Comments in the current ...
- 167k
5
votes
Accepted
Index is not used with table inheritance
All of this is unrelated to inheritance and partitioning. It's about indexing and query plans in general.
The row size is much bigger for your second try: width=157 vs. width=46. Postgres will even ...
- 167k
5
votes
Accepted
PostgreSQL and table inheritance
There is nothing in your question that would speak against inheritance.
One major limitation of the current Postgres implementation of inheritance is that FK, PK and UNIQUE constraints only apply to ...
- 167k
4
votes
Conceptual ERD Multi-table many to many, or possibly recursive?
I think you are trying to blend together concepts from object modeling and concepts from data modeling in a way that isn't helping you to clarify your own understanding of the problem. I hope I can ...
- 4,183
4
votes
Abstract classes in SQL Server. Are they even possible?
There is nothing like this in SQL Server.
What tool are you building and maintaining your schema in though, will you be using database projects? If you went the "manual" method, while there is no ...
- 3,498
3
votes
Accepted
Inheritance of "dictionary tables"
Using one supertable Tag will give you less tables in your schema, but you will basically lose guarantees about referential integrity. If one column in another table must have one 'colour' value, you ...
- 13.1k
3
votes
Accepted
Postgres inherit from one role and not another
I solved this by nesting the two roles together.
CREATE role elevated_role nologin noinherit;
GRANT ALL PRIVILEGES ON ALL tables IN SCHEMA db to elevated_role;
GRANT usage ON SCHEMA db ...
- 83
3
votes
Accepted
Postgresql inheritance based database design
Whether you are better off with single-table-inheritance or class-table-inheritance really depends on the particulars of your case. The performance advantage can go either way. The difficulties ...
- 4,183
3
votes
Accepted
Enhanced ER Model Supertype / Subtype modeling issue with inheritance and recursion
I want to call attention to one of your business rules: Representative is Customer and is Company.
This sounds like a superclass/subclass situation to me, and I think you have the EER diagram well in ...
- 4,183
2
votes
How to enforce data consistency across one-to-one relationship in an inheritance hierarchy
One method is to introduce a PersonType attribute. Using this as a composite key along with a check constraint and foreign key in the Student and Teacher tables will ensure a row for a given person ...
- 26.7k
2
votes
How to enforce data consistency across one-to-one relationship in an inheritance hierarchy
Add a student/teacher attribute to Person. Since this attribute is dependent of the key in Person (whatever that is), no Person can be both a Teacher and a Student. Now it is a matter of guaranteeing ...
- 22.8k
2
votes
Accepted
Partial unique constraint spanning multiple tables in postgres
Postgres directly supports table inheritance, which does the trick:
CREATE TABLE base_possession (
possession_id SERIAL PRIMARY KEY
);
CREATE TABLE possession (
possession_id INTEGER NOT NULL ...
- 211
2
votes
Accepted
Finding a good DB design for my project
In the comments you mention that a UserInput is either a Msg or an Action and not related to many actions or to many messages. The first model does not accurately describe this relationship.
You ...
- 96.2k
2
votes
Record inheritance in mySQL
If you want to store it as a hierarchical structure, the pattern that would best serve you is the "Closure Table" pattern, which allows for subtree operations (select/update/delete all descendants of ...
2
votes
Using table inheritance instead of mapping tables
Many reasons to avoid, and all boil down to violating the principles of the relational model. Most obviously, the child tables are bags, not relations, and the lack of foreign keys violate the ...
- 196
2
votes
PostgreSQL: Multiple Inheritance tables (one child to two parents)
I advise never using INHERITS, even in simple single-inheritance cases
Not sure why you think this would not be possible.
I would certainly never do this butttt.... you have Multiple Inheritance too ...
- 60.4k
2
votes
Accepted
Abstract classes in SQL Server. Are they even possible?
While correct that PostgreSQL does indeed support this request, SQL Server has no similar ability. That leaves you with either placing all of the "common" columns in each table, or having a "base" ...
- 67.7k
2
votes
DB model strategy to override field value
You can create a view like:
create view all_config as
select y.id, x.accountId, y.config
from accounts x
cross join defaultconfig y
where not exists (
select 1 from accountconfig z
where x....
- 22.8k
2
votes
Accepted
Database relationship inheritance
Your question is typically too broad to fit in a post here, but I'll draw out some lines that might help you to get a start.
A vehicle can be either a boat or a car. A good place to start is to think ...
- 22.8k
2
votes
Accepted
Progressive child tables scan in PostgreSQL?
The problem is that it scans all child tables and only then does the
LIMIT 10. Indexes for all these tables are quite big and don't fit
into memory, so it takes up to 20 seconds to complete this ...
- 36.5k
2
votes
Accepted
Inheritence and Primary Key Constraints
The two tables person and praktikant in your example are two distinct tables. Inserting rows into praktikant does not insert rows in person, but
SELECT ... FROM person;
is effectively the same as
...
- 42.7k
2
votes
Postgres role does not inherit permissions from its member role
Thanks to one of my colleague, I could resolve this issue. You have to first grant permission to the schema. EXECUTE format('GRANT USAGE ON SCHEMA %I TO %I', schem, role_name);.
Until then things won'...
- 21
1
vote
Views that inherit a table and syntax in PostgreSQL
Views cannot inherit.
Only tables can.
Aside: the query for CREATE VIEW does not need parentheses. And your whole SELECT is needlessly convoluted and buggy. Untangled and without the invalid ...
- 167k
Only top scored, non community-wiki answers of a minimum length are eligible