Questions tagged [subtypes]
Questions about data that is organized into types and subtypes.
91
questions
0
votes
0
answers
29
views
Supertype/Subtype how to decide on representation scheme?
This textbook lists 3 options for dealing with a supertype/subtype relationship. However, it doesn't provide a lot of "reasons" to choose one option over the others. What sorts of things ...
- 121
0
votes
1
answer
62
views
What is the most straightforward way to create subtypes and supertypes at the same time?
I hope this isn't too basic a question, but I am hoping that the DB engineers here will help me out. Do relational DBs usually have a straightforward way of doing the following?:
Create a supertable ...
0
votes
0
answers
15
views
Class Inheritance and subtypes
I have researched a lot about table inheritance and am still confused about how I should model my design.
My app is like a social media virtual world. The people of the system are
users (who explore ...
- 1
0
votes
0
answers
299
views
MySQL query: multiple select with different conditions and LIMIT for each, but also LIMIT whole query + paginator friendly
I would like to write SQL query (for MySQL) that is able to SELECT
x rows by condition1 from table1
y rows by condition2 from table1+join1
z rows by condition3 from table1+join2
But on every page (...
- 1
0
votes
1
answer
464
views
Insert into table with polymorphic association in database
I want to design a database where some tables have multiple foreign keys.
I am looking for the best design approach.
I found out that this kind of problem is called "polymorphic association" ...
1
vote
1
answer
110
views
Advice on Database Design
context :
Teachers create problems to their Students
A Problem have Numerals(Questions)
A Numeral(Question) have Steps to guide the student to the answer
Choice represent the type of mathematical ...
- 13
1
vote
3
answers
1k
views
How can I design Person table in MySQL?
I have two person type: Users and Clients.
Users can login the panel but clients can't login the panel.
My current database: (but this design not good for me. I need to connect these tables to the ...
- 31
1
vote
1
answer
947
views
Single table with multiple NULL columns vs multiple tables
I've read this. But I'm not sure if I understood correctly or my situation is slightly different.
So, Here is my situation: I've a single table with customer's delivery addresses and there are two ...
- 113
-1
votes
1
answer
425
views
Is there a best practice design for handling different types of the same object which have different properties?
This may be a rather naive question and I am not even sure if I am asking it in the best way, so my apologies if not.
I would like to know if there is a standard, or best way to handle cases where ...
- 421
1
vote
0
answers
702
views
Schema for social network using the supertype/subtype pattern
Scenario
I'm trying to come up with a schema for a kind-of social network app, where users can publish Posts (and photos inside Posts), and also manage a private Diary that has also Posts (not sure ...
- 123
0
votes
0
answers
21
views
Trouble with inheritance: Separate table implementation for disjoint entities and common ID
I currently have four disjoint entity types (competition, talk, award, publication) that are to be converted to tables. In order to manage certain other data for each, i.e. documents related to each ...
1
vote
1
answer
341
views
Inheritance - What's better: three "equal" tables or one with a type column?
I'm having an existencial doubt: I have three conceptual entity types on my logic:
- distributors
- business_groups
- clients
All of three are "companies", and will have the same columns. The ...
- 111
2
votes
1
answer
484
views
Reviewing the design of a database about animals of two distinct species
I am creating a small database using SQL Server Express 2017.
There are two animals (Foxes and Elephants).
The Foxes table will have the columns: Identifier,
Species, BodyLength, and FurColour.
The ...
user182143
0
votes
0
answers
33
views
Does "Inheritance" Make Sense with This Requirement?
I'm using Oracle 12c. Given an existing table (we call it TBL_M), and based on the criteria below, I was wondering if using inheritance makes sense to lay out the entities:
Information for one group ...
- 101
6
votes
3
answers
2k
views
Solving supertype-subtype relationship without sacrificing data consistency in a relational database
Trying to get better at designing databases, I noticed I'm always stuck trying to solve variations of the exact same problem.
Here is an example using common requirements:
An online store sells ...
- 61
0
votes
2
answers
2k
views
Dealing with subtypes and supertypes in Database design
I'm currently doing a database project for a class and I am having some difficulty dealing the subtypes and supertypes.
This is the list of entities and their attributes:
Piece (PIECE_ID, Description)...
- 101
0
votes
1
answer
1k
views
EER Diagram - subclasses which are neither disjoint nor fully overlapping
Suppose we are writing an enhanced entity-relationship diagram (EERD) for a company's database. This company offers two services A and B. If the two services were disjoint, then this might look ...
- 103
0
votes
1
answer
2k
views
Designing MySQL Database For Two Different User Types (Staff and Client)?
I'm creating a web-app that will connect to a MySQL database. This app will have 2 users: a Staff and Client that can both log in from the same login page.
Both Staff and Client share the following ...
- 39
1
vote
1
answer
2k
views
Extended Entity-Relationship Diagram: Do the sub classes have foreign keys or primary keys?
Consider this example (they are disjoint, by the way):
There are two scenarios which I am guessing:
While translating it to relational tables,
the rows in PROGRAMMER are unique which do not exist ...
- 143
1
vote
1
answer
322
views
How to handle a table schema with a new relationship subtype in an interviews scenario?
I have a table of "interviews" and "interview reviews" for both the interviewer and the interviewee. When the project was started there was only the need for one type of review, which is shown below
...
- 109
3
votes
2
answers
105
views
What is the correct way to enforce unique "IS-A" relationship?
Let's say we have a Person relation, and with that Employee which IS-A Person, Manager, which ISA Person as well.
What is the correct way to ensure that a Manager isn't an Employee and vice versa ?
...
1
vote
0
answers
392
views
How to create entity-relationship diagram for tables that can potentially be united by an "IS A" relationship?
I have this schema:
create table hall(
building-id int,
hall-id int,
capacity int,
primary key(building-id, hall-id)
);
create table semester(
sem-id char(5),
start-date date,
eend-...
- 195
1
vote
1
answer
93
views
How to make Event (like a Party) Sources mutually exclusive?
I'm designing an entity-relationship diagram (ERD) for a marketplace of events. An event can be created by a user, in which case the Event table must have owner_id or we pull it from an API in which ...
- 11
0
votes
1
answer
1k
views
Inserting into subtype table values from supertype table in oracle
I had created two object types and tables based on that types in oracle:
create or replace type TEMPLOYE as object(
num_emp integer,
nom_emp varchar2(40),
prenom_emp varchar2(50),
adresse_emp ...
1
vote
0
answers
62
views
ERD Design issue - general/central projects table
I have multiple different “project manager” apps as: Work Projects app manager, Home Projects app manager,...
Each app has its own DB i.e separate ER diagram.
Each app has it's own [projects] table(...
- 141
2
votes
2
answers
311
views
Internationalization of a Supertype-subtype
First of all, my apologies for tieing this question with another one, but that was the best way I could think to keep it simple and focused
Months ago I was introduced to the supertype-subtype as ...
- 295
3
votes
2
answers
3k
views
Filtered foreign keys
I have two tables:
Contact (ID, Type);
Address (ID).
Is it possible to define a foreign key from Address (ID,'A') to Contact (ID,Type)?
- 259
9
votes
2
answers
5k
views
Developing a database for a funds transfers business where (a) people and organizations can (b) send and receive money
In the business context of relevance, both members and organizations need to have an account for funds. Funds can be transferred
from member to member,
from member to organization,
from organization ...
- 93
2
votes
0
answers
167
views
Best Practice - Database design for 'conditional join' [duplicate]
I need to create a database to store Jobs.
Each Job can have 1-* Step
Each step is classified as one of either Type A, B or C.
Each Type has specific details and other tables that join on them so ...
- 121
1
vote
1
answer
1k
views
Can a sublass have two parents (two entity types that are connected with it?)
I'm kind of new to ERDs and stuff, just wondering if I'm correct when my (conceptual) entity relationship diagram has two entity types connected to one subclass (so the subclass inherits data from ...
- 13
2
votes
2
answers
89
views
Basic RDBMS table relations
If you have a design of a "master table" and several "subtables", is it better* that the master table "link" to each of the sub tables, the subtables link to the master, or for bi-directional links be ...
- 968
2
votes
2
answers
884
views
Modelling users and groups - but with multiple types of groups
I'm modelling a user management system in PostgreSQL that is fairly typical in that it consists of users, groups and permissions (and the relationships between them).
However, its seems I'm dealing ...
- 121
3
votes
0
answers
2k
views
Supertype table with disjoint subtypes in MySQL
I want to have base parent table with few disjoint subtypes. To illustrate what I mean:
CREATE TABLE issues (
issue_id SERIAL PRIMARY_KEY,
issue_type CHAR(1) CHECK (issue_type IN ('B', 'F')),
...
- 31
2
votes
1
answer
151
views
Is it possible to represent a ‘mutual IS-A’ relationship in an entity-relationship diagram?
Is the following scenario correct and why:
A and B are two entities
B is-a A
A is-a B
And what if A, B were relationship?
- 123
2
votes
3
answers
458
views
How can I enforce a one-to-zero-or-one relationship with multiple types?
I am creating a database structure to represent a football league, but there is a certain part that I do not know how to solve, that is:
A member and he can be a referee, a management or a player (...
- 23
5
votes
3
answers
8k
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
6
votes
3
answers
5k
views
One-to-"Zero or One" Relation
I have the following requirements:
Every transaction has one of the following types; debit, credit, deposit, or withdraw.
Debit or credit transactions must have a linked invoice record and no bank ...
17
votes
2
answers
14k
views
How to model an entity type that can have different sets of attributes?
I’m having some trouble in recreating a database with a one-to-many (1:M) relationship between Users and Items.
This is pretty straightforward, yes; however, each Item belongs to a certain Category (e....
- 295
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 ...
- 151
1
vote
1
answer
750
views
Can a weak entity type be involved in an "is-a" relationship?
Considering the scenario depicted in the diagram below, as the title says, can a weak entity type take part in an "is-a" relationship?
- 143
21
votes
3
answers
5k
views
Implementing subtype of a subtype in type/subtype design pattern with mutually exclusive subclasses
Introduction
In order for this question to be useful for future readers I will use the generic data model to illustrate the problem I face.
Our data model consists of 3 entities, which shall be ...
2
votes
0
answers
672
views
What is the difference between (Superclass/Subclass) Total and Partial Specialization?
What is the difference between total and partial specialization of superclass/subclass (or supertype-subtype) structures in enhanced entity-relationship modeling?
- 21
2
votes
1
answer
1k
views
Conditional relationships? One-to-many relationship using a pivot table for only a type of entity
I have the following tables:
users:
- id
- name
- user_type_id (references id on user_types)
neighborhoods:
- id
- name
homes:
- id
- house_number
- neighborhood_id (references id ...
- 165
8
votes
1
answer
1k
views
Is my implementation of type/subtype design pattern (for mutually exclusive subclasses) correct?
Introduction
In order for this question to be useful for future readers I will use the generic data model to illustrate the problems I face.
Our data model consists of two entities, which shall be ...
2
votes
2
answers
3k
views
Store table names to reference other tables
I'm designing a webapp where users can add widgets to their pages. Right now I have only two types of widgets that are different in nature, so they have different attributes, but both of them are used ...
- 53
2
votes
1
answer
2k
views
How to JOIN a table with a name that equals to a determined value in MySQL?
The model of the (PHP & MySQL based) Zend Framework 2 application I’m currently working on contains a inheritance similar to the one described here.
To map this to the database I’m using the ...
- 93
5
votes
1
answer
3k
views
Many to many between lots of tables?
TL;DR: How could I set up many-to-many relationship between that many tables and is it viable/recommended?
Each body type has its own table
Each car in each body type table can has several features
...
- 153
2
votes
0
answers
72
views
Looking for suggestions to model a database of Electrical Components
We have a database of electrical components and I am trying to make sure I have a good design. I am not a database designer (my comfort zone is designing with these hardware components), so I am ...
2
votes
2
answers
144
views
How do I guarantee inventory numbers are unique across all tables?
I have an assortment of physical objects which need to be inventoried and each type of object has it's own table. For two example objects, let's use 'swords' for the table containing different swords ...
- 219
3
votes
1
answer
2k
views
How to represent the optionality of a many-to-one mandatory relationship from the Parent to its two Child entity types in an ERD?
I have a Parent entity type with two Child entities, with a relationship cardinality of many-to-one (or M:1) from Parent to Child.
The business rules say that:
It is mandatory that the Parent ...
- 31