Questions tagged [subtypes]

Questions about data that is organized into types and subtypes.

Filter by
Sorted by
Tagged with
20
votes
3answers
3k 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 ...
18
votes
4answers
8k 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(...
13
votes
4answers
50k 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 table)...
12
votes
3answers
5k 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 ...
11
votes
4answers
23k 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 ...
11
votes
2answers
5k 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 (...
11
votes
2answers
5k 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 ...
9
votes
4answers
1k 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 ...
8
votes
1answer
1k 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 ...
7
votes
2answers
1k 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 ...
7
votes
1answer
636 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 ...
5
votes
3answers
4k 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 ( ...
5
votes
2answers
596 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 ...
5
votes
1answer
3k 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 ...
5
votes
3answers
2k 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 ...
4
votes
2answers
997 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 ...
4
votes
2answers
1k 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 ...
4
votes
1answer
665 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 ...
4
votes
3answers
153 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, ...
4
votes
2answers
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: ----------------- ...
4
votes
1answer
922 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: ...
4
votes
2answers
149 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 ...
4
votes
2answers
1k 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 "...
3
votes
2answers
894 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)?
3
votes
2answers
77 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 ? ...
3
votes
3answers
2k 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 ...
3
votes
1answer
629 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 ...
3
votes
1answer
1k 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 ...
3
votes
3answers
709 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 ...
3
votes
1answer
1k 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 ...
3
votes
1answer
379 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 ...
3
votes
1answer
905 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 ...
3
votes
1answer
2k 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) ...
3
votes
0answers
899 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')), ...
3
votes
0answers
121 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 ...
3
votes
1answer
47 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 ...
2
votes
2answers
959 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 "...
2
votes
2answers
176 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 ...
2
votes
2answers
48 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 ...
2
votes
1answer
1k 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 ...
2
votes
1answer
6k 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 ...
2
votes
2answers
217 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 ...
2
votes
3answers
106 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 (...
2
votes
1answer
1k 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, ...
2
votes
1answer
2k 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 ...
2
votes
1answer
86 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?
2
votes
1answer
72 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 (...
2
votes
2answers
1k 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 ...
2
votes
1answer
921 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 ...
2
votes
0answers
523 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?