Questions tagged [subtypes]

Questions about data that is organized into types and subtypes.

Filter by
Sorted by
Tagged with
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 "...
0
votes
1answer
173 views

How to create a SELECT statement involving a subtype

Okay, so all I'm trying to do here is "list" the managers who supervised the employee that charged a customer $100 for a rental on November 1st, 1998. My issue is that I don't know how to select the ...
5
votes
2answers
176 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
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 ...
-1
votes
1answer
20 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
66 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
2answers
625 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 ...
14
votes
4answers
52k 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)...
1
vote
1answer
247 views

Category entity relationship / subtype relationships design

I am looking to implement a generic comment and file attachment facility within a web application which would be used across various parts and allow users to provide comments and add attachments to ...
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
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 ...
0
votes
0answers
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 ...
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 ...
0
votes
2answers
162 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
255 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 ...
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 ...
0
votes
1answer
302 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
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 ...
1
vote
1answer
204 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 ...
6
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 ...
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 ...
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 ...
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 ...
1
vote
1answer
292 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?
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-...
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 (...
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 ...
3
votes
1answer
934 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 ...
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 ...
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 ...
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 ...
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 ...
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(...
0
votes
1answer
512 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 ...
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 ...
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, ...
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 ...
2
votes
2answers
181 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
936 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)?
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 ( ...
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 ...
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 ...
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 ...
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 ...
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(...
3
votes
0answers
957 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 (...