Questions tagged [subtypes]

Questions about data that is organized into types and subtypes.

Filter by
Sorted by
Tagged with
0 votes
1 answer
183 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" ...
user avatar
1 vote
1 answer
91 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 ...
user avatar
0 votes
3 answers
385 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 ...
user avatar
0 votes
1 answer
352 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 ...
user avatar
  • 103
-1 votes
1 answer
118 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 ...
user avatar
  • 411
1 vote
0 answers
413 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 ...
user avatar
0 votes
0 answers
17 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 ...
user avatar
1 vote
1 answer
101 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 ...
user avatar
  • 111
2 votes
1 answer
216 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
28 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 ...
user avatar
  • 101
6 votes
3 answers
1k 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 ...
user avatar
0 votes
2 answers
983 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)...
user avatar
  • 101
0 votes
1 answer
707 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 ...
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 ...
user avatar
  • 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 ...
user avatar
  • 143
1 vote
1 answer
230 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 ...
user avatar
  • 109
3 votes
2 answers
97 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 ? ...
user avatar
1 vote
0 answers
289 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-...
user avatar
  • 195
1 vote
1 answer
74 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 ...
user avatar
  • 11
0 votes
1 answer
990 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 ...
user avatar
1 vote
0 answers
59 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(...
user avatar
2 votes
2 answers
220 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 ...
user avatar
3 votes
2 answers
2k 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)?
user avatar
9 votes
2 answers
4k 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 ...
user avatar
  • 93
2 votes
0 answers
165 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 ...
user avatar
  • 121
1 vote
1 answer
844 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 ...
user avatar
  • 13
2 votes
2 answers
68 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 ...
user avatar
  • 963
1 vote
2 answers
467 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 ...
user avatar
  • 111
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')), ...
user avatar
  • 31
2 votes
1 answer
130 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?
user avatar
  • 123
2 votes
3 answers
266 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 (...
user avatar
  • 23
5 votes
3 answers
7k 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 ( ...
user avatar
  • 159
6 votes
3 answers
4k 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 ...
user avatar
15 votes
2 answers
11k 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....
user avatar
4 votes
2 answers
2k 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 ...
user avatar
1 vote
1 answer
511 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?
user avatar
21 votes
3 answers
4k 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 ...
user avatar
2 votes
0 answers
584 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?
user avatar
2 votes
1 answer
990 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 ...
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 ...
user avatar
2 votes
2 answers
2k 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 ...
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 ...
user avatar
3 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 ...
user avatar
  • 133
2 votes
0 answers
64 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 ...
user avatar
2 votes
2 answers
134 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 ...
user avatar
  • 179
3 votes
1 answer
1k 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 ...
user avatar
5 votes
2 answers
1k 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 ...
user avatar
  • 103
22 votes
4 answers
19k 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(...
user avatar
  • 323
0 votes
2 answers
1k views

MySQL Union not showing 2nd query result set

I am doing a union of 2 queries and trying to fetch data on two different columns, however the union is not fetching the value from 2nd result set. Can you please guide me on this. select * From ...
user avatar
  • 21
3 votes
0 answers
128 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 ...
user avatar
  • 155