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
26 votes
4 answers
82k views

How to model inheritance of two tables MySQL

I have some tables where I store data and depending on the type of person (worker / civil) that did a job I want to store it in an event table, now these guys rescue an animal (there is an animal ...
edgarmtze's user avatar
  • 361
22 votes
4 answers
22k views

Best design to reference multiple tables from single column?

Proposed schema First and foremost, here is an example of my proposed schema to reference throughout my post: Clothes ---------- ClothesID (PK) INT NOT NULL Name VARCHAR(50) NOT NULL Color VARCHAR(...
youngrrrr's user avatar
  • 323
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
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
15 votes
3 answers
9k views

Modeling a scenario in which each Music Artist is either a Group or a Solo Performer

I have to design an entity-relationship diagram (ERD) for a business context that involves the delineation of music artists, as I will detail below. Scenario description An Artist has a Name, and ...
Boshir's user avatar
  • 151
14 votes
4 answers
27k views

Supertype/Subtype deciding between category: complete disjoint or incomplete overlapping

I'm building an inventory database that stores IT hardware, such as desktop computers, laptops, switches, routers, mobile phones, etc. I'm using supertype/subtype pattern, where all devices are stored ...
TheSecretSquad's user avatar
14 votes
2 answers
9k views

How should I model an "either/or" relationship?

Say I have an entity named Software and two sub-types FreeSoftware and NonFreeSoftware. The NonFreeSoftware entity has attributes such as purchase date, vendor, etc.. The FreeSoftware entity has ...
jl6's user avatar
  • 805
10 votes
4 answers
2k views

Don't know how to transform variable entity into relational table

INTRODUCTION AND RELEVANT INFORMATION: The following example illustrates the problem I face: Animal has a race, which can be a cat or a dog. Cat can be either Siamese or Persian. Dog can be a German ...
AlwaysLearningNewStuff'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
8 votes
1 answer
3k views

Database schema for entities with two possible owner/parent types?

I'm using PostgreSQL with Sequelize as my ORM. I have one type, User. The second type is Group, which can have any number of users associated with it through a GroupMemberships table. Users can also ...
Jakemmarsh's user avatar
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
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
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
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
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
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
5 votes
2 answers
2k views

Best way to linking different entities to a single table

I'm running for office and have created a web app for tracking my door knocks to voters at their homes. The database contains a table called voters that contains all the necessary information about ...
StevieD's user avatar
  • 183
5 votes
1 answer
848 views

Implementing a one to one (1:1) relationship between Person and User

Scenario description I have this person table as super parent (or supertype), id firstname lastname email telephone ... ... and user table as a child (or subtype) id person_id (FK) password username ...
Run's user avatar
  • 153
5 votes
1 answer
5k views

How to represent class table inheritance (current DBMS-specific way please)?

I want to implement class either-or table inheritance (Account, CatAccountDetails, DogAccountDetails) in SQL Server. I found a recommendation for MySQL: How do I map an IS-A relationship into a ...
shannon's user avatar
  • 406
5 votes
1 answer
2k views

How to model an “is a” relationship in an ERD using Crow’s foot notation?

I have to create an entity-relationship diagram (ERD) for an assignment. I am doing this in a particular drawing tool with the Crow’s Foot notation. There is however one thing I cannot figure out: ...
Some_student'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
4 votes
1 answer
2k views

Modeling Subclass (or Subtype) Constraints in an Entity-Relationship Diagram

Scenario description Can someone help me out on how to model the situation that follows: There is a superclass (or supertype) called PRODUCTS, which has the subclasses (or subtypes) named FOOD and ...
Otaxar's user avatar
  • 143
4 votes
3 answers
227 views

Some users can have a fixed amount of additional fields, EAV or not?

Say I have to store data about two different types of person roles or occupations, i.e., Consumers and Merchants, and both types of roles share a single base table called User. In this respect, ...
JC Lee's user avatar
  • 205
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: ----------------- ...
Saeed Neamati's user avatar
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 ...
Zalom's user avatar
  • 143
4 votes
4 answers
5k views

Two ways to define may-have-a (1..0-1) relationship

Today I have discovered for myself that may-have-a, one-to-zero-or-one, (1-0..1) relationship can be implemented in two different ways: Let A be the "main" table (left side of ..) and B be the "...
porton's user avatar
  • 745
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
3 votes
3 answers
4k views

Database design: 4 types of users but have different functionality , separate or one table?

I have 4 types of users: Admins , normal user , company , service provider Admins and normal user share some attributes (id .first name ,last name ,phone ,mail) company and service provider share ...
ramyMorad's user avatar
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
3 votes
1 answer
10k views

Representing a Superclass/Subclass (or Supertype-Subtype) relationship in a MySQL Workbench diagram

Situation I am trying to figure out how to represent a one-to-one (1:1) relationship regarding a superclass/subclass or supertype-subtype structure in an entity-relationship diagram (ERD) of the kind ...
Matchoo's user avatar
  • 33
3 votes
1 answer
2k views

Is this acceptable practice for a many to many?

As a newbie in database design, I have a question about many-to-many relationship practice. This question is actually a follow up question of this question, but I found it too big to explain in a ...
Vahx's user avatar
  • 225
3 votes
3 answers
1k views

Field types database design best practice

I am creating a cms system where the user can create fields to use in the templates of the website. I want to create different types like 'string', 'html', 'integer' etc. The user must also have the ...
Jerodev's user avatar
  • 131
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
3 votes
1 answer
407 views

Should I put common fields in each specific tables or one common table then join when data selecting?

Assuming there are a few categories: blog, video, gallery. Each of them contains post data like id, title, content, author_id, created_at, but different category have few more specific field: video ...
tom10271's user avatar
  • 142
3 votes
1 answer
3k views

Supertypes and subtypes

I am trying to design a database that has supertype/subtypes. I have the following users: - basic user - business user - administrator (super user). All three users share a common table (users) ...
Cristian's user avatar
  • 203
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
3 votes
0 answers
136 views

How to relate a "parent common" table with its (possibly many) "subtype" tables?

I am having a bit of a brain freeze. I have a table whose properties are the root of four distinct elements, for example: I need to relate SystemType to a SystemCommon, where one SystemCommon can ...
StixO's user avatar
  • 177
3 votes
1 answer
57 views

Confused about what to use to create the data

I want to create a database for myself. The data has complex grouping. I don't know which system to use to create my database. The data is in groups and sub-group forms, it almost has 5-6 layers of ...
Smeet Soni's user avatar
2 votes
2 answers
1k views

Normalising shared entities and applying constraints

I'm looking for a specific "best practice" or "pattern" concerning entities that are shared between different entities, having a relation to one of many. For example, one may have the generic entity "...
Louis Somers'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
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
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
2 votes
2 answers
276 views

Designing a scenario where a primary key could be of two types

I have a table: all_info, where it holds the users identification. The id of this table can be of two types (just one or another, never both), suppose: int and varchar, but one table can't have two ...
Rafael Barros's user avatar
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
2 votes
1 answer
3k views

Foreign Key Constraint over two tables

I am trying to put the following entity-model into practice: We have a list of Payments, each Payment belongs to a certain store and is of a certain payment type (e.g. 10€ maintenance cost in Store1, ...
Falco's user avatar
  • 153
2 votes
1 answer
3k views

Relationships between subtypes of an entity

I have a very big (hundred of milions rows) table which represent an entity (event) having many subtypes (different kind of recorded events). Each row is identified by an id (obj_id) and has an ...
mic.sca's user avatar
  • 71
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
1 answer
100 views

Using the same table for entities that are almost alike when it breaks foreign key integrity?

I'm designing a website with a database that should support events that a user can sign up for, fx. a concert. Users can sign up using our internal registration system, or using an external website (...
soren.qvist's user avatar
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
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