Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [subtypes]

Questions about data that is organized into types and subtypes.

Filter by
Sorted by
Tagged with
0 votes
0 answers
125 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 ...
Him's user avatar
  • 133
0 votes
1 answer
271 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 ...
Logos Masters's user avatar
0 votes
0 answers
342 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 (...
Nick's user avatar
  • 1
0 votes
1 answer
546 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" ...
Alireza Azadi's user avatar
1 vote
1 answer
115 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 ...
joaortizro's user avatar
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 ...
Fırat Kaya's user avatar
1 vote
1 answer
1k 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 ...
Bipul Roy's user avatar
  • 113
-1 votes
1 answer
518 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 ...
Gravitate's user avatar
  • 421
1 vote
0 answers
752 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 ...
BinaryVeil's user avatar
0 votes
0 answers
23 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 ...
averagedeveloper's user avatar
1 vote
1 answer
451 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 ...
sevenlops's user avatar
  • 111
2 votes
1 answer
633 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 ...
user avatar
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 ...
NuCradle's user avatar
  • 101
6 votes
3 answers
3k 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 ...
user174739's user avatar
0 votes
2 answers
3k 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)...
J.Doe's user avatar
  • 101
0 votes
1 answer
2k 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 ...
knrumsey's user avatar
  • 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 ...
Jae Bin's user avatar
  • 39
1 vote
1 answer
3k 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 ...
MrRobot9's user avatar
  • 143
1 vote
1 answer
355 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 ...
Joff's user avatar
  • 109
3 votes
2 answers
107 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 ? ...
Pierre-Antoine Guillaume's user avatar
1 vote
0 answers
439 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-...
Yos's user avatar
  • 195
1 vote
1 answer
101 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 ...
Seio's user avatar
  • 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 ...
boussadjra brahim's user avatar
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(...
Goce Ribeski's user avatar
2 votes
2 answers
327 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 ...
user5613506's user avatar
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)?
Andrew Hill's user avatar
9 votes
2 answers
6k 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 ...
aarnes's user avatar
  • 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 ...
Intern87's user avatar
  • 121
1 vote
1 answer
2k 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 ...
James's user avatar
  • 13
2 votes
2 answers
96 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 ...
mpag's user avatar
  • 968
2 votes
2 answers
987 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 ...
Rob's user avatar
  • 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')), ...
Vasek's user avatar
  • 31
2 votes
1 answer
153 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?
Angelo's user avatar
  • 123
2 votes
3 answers
481 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 (...
Gro's user avatar
  • 23
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 ( ...
Dmitri's user avatar
  • 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 ...
Yosua Lijanto Binar's user avatar
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....
user5613506's user avatar
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 ...
Petros Apotsos's user avatar
1 vote
1 answer
823 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?
Pegasus008's user avatar
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 ...
AlwaysLearningNewStuff's user avatar
2 votes
0 answers
708 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?
user2331051's user avatar
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 ...
Vic's user avatar
  • 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 ...
AlwaysLearningNewStuff's user avatar
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 ...
mgaldieri's user avatar
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 ...
automatix's user avatar
5 votes
1 answer
4k 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 ...
Oscar's user avatar
  • 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 ...
Justin Jansen's user avatar
2 votes
2 answers
147 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 ...
foobrew's user avatar
  • 264
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 ...
Shaneydev's user avatar
5 votes
2 answers
2k views

Modeling a Car Dealership context in an Entity-Relationship diagram

I am creating a conceptual database model for a car dealership. I have spoken to the client, gathered all the requirements, and thought I had my Entity-Relationship diagram all correct; however, I ...
AndrewS's user avatar
  • 103