Questions tagged [subtypes]

Questions about data that is organized into types and subtypes.

Filter by
Sorted by
Tagged with
-1
votes
1answer
19 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 ...
1
vote
0answers
64 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 ...
0
votes
0answers
16 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
1answer
48 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 ...
1
vote
1answer
86 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 ...
0
votes
0answers
27 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 ...
4
votes
2answers
167 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 ...
0
votes
2answers
158 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)...
0
votes
1answer
248 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 ...
0
votes
1answer
298 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 ...
1
vote
1answer
201 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 ...
1
vote
1answer
73 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 ...
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 ? ...
1
vote
0answers
177 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-...
1
vote
1answer
43 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 ...
0
votes
1answer
507 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
0answers
47 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(...
2
votes
2answers
180 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 ...
3
votes
2answers
930 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)?
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 ...
1
vote
0answers
86 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 ...
1
vote
1answer
413 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 ...
2
votes
2answers
49 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 ...
0
votes
2answers
120 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 ...
3
votes
0answers
951 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')), ...
2
votes
1answer
87 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
3answers
121 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 (...
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
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 ...
11
votes
2answers
6k 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 (...
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 ...
1
vote
1answer
291 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?
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 ...
2
votes
0answers
527 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?
2
votes
1answer
807 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 ...
7
votes
1answer
658 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
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
951 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
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
0answers
62 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
2answers
126 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 ...
3
votes
1answer
929 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 ...
4
votes
2answers
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 ...
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(...
0
votes
2answers
619 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 ...
3
votes
0answers
122 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 ...
4
votes
1answer
675 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 ...
1
vote
1answer
69 views

Need help making a sane database schema

I am writing a backend for an application that has 4 main types of content: a message, a post, a thread, and a thread reply. Right now, these are all stored in separate MySQL tables: a table for each ...
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 ...
0
votes
1answer
728 views

Derived classes with same base class and class specific ids

I have three classes in my project. One base class and two derived classes. public class Animal { public long Id { get; set; } public DateTime Created { get; set; } public string Name { ...