Skip to main content
Share Your Experience: Take the 2024 Developer Survey
24 votes
Accepted

How to model an entity type that can have different sets of attributes?

According to your description of the business environment under consideration, there exists a supertype-subtype structure that encompasses Item —the supertype— and each of its Categories, i.e., Car, ...
MDCCL's user avatar
  • 8,520
21 votes

Implementing subtype of a subtype in type/subtype design pattern with mutually exclusive subclasses

According to Martin Fowler, there are 3 approaches to the problem of table inheritance: Single Table Inheritance: One table represents all types. Unused attributes are NULLed. Concrete Table ...
Remus Rusanu's user avatar
  • 51.9k
15 votes
Accepted

Developing a database for a funds transfers business where (a) people and organizations can (b) send and receive money

If the intention is to build a relational database, it is really helpful to first carry out (a) an analysis of the business context of interest —in order to delineate a conceptual schema— in terms of ...
MDCCL's user avatar
  • 8,520
10 votes
Accepted

Best design to reference multiple tables from single column?

I personally don't like to use a multi-table schema for this purpose. It's hard to ensure integrity. It's hard to maintain. It's difficult to filter results. I've set a dbfiddle sample. My proposed ...
McNets's user avatar
  • 23.8k
9 votes

Using table inheritance instead of mapping tables

Inheritance is one of those features that I wouldn't touch. AFAIK, it's used internally for replication and partitioning in some capacity. I'm not sure if it was even designed with the intent to be ...
Evan Carroll's user avatar
  • 63.6k
8 votes
Accepted

Filtered foreign keys

Assuming that you want to implement a supertype/subtype relationship between the two tables (an Address is a Contact), you can, with a column having a fixed value of 'A' and use it for the foreign key:...
ypercubeᵀᴹ's user avatar
7 votes
Accepted

Modeling a Car Dealership context in an Entity-Relationship diagram

Service, inspection, and repair are all instances of some work. If you really do need service, inspection, and repair as separate entities, create a super-entity Work, which would have a 1..* ...
mustaccio's user avatar
  • 26.2k
7 votes
Accepted

Is my implementation of type/subtype design pattern (for mutually exclusive subclasses) correct?

Yes, the design looks great. Minor notes: You could use TINYINT, instead of the INT for the ClassTypeID. Or even CHAR(1) and have 'A' and 'B' instead of 1 and 2. 1 byte instead of 4 means you are ...
ypercubeᵀᴹ's user avatar
7 votes
Accepted

Implementing subtype of a subtype in type/subtype design pattern with mutually exclusive subclasses

Insofar as this Question is a continuation of Is my implementation of type/subtype design pattern (for mutually exclusive subclasses) correct?, which is itself a continuation of Don't know how to ...
Solomon Rutzky's user avatar
6 votes

Using table inheritance instead of mapping tables

The mentioned deficiencies are not a reason not to use inheritance! Inheritance works here similar to Class-Inheritance with independent Objects. You may have a class/table 'fruits' and a class/table '...
Mihail Gershkovich's user avatar
6 votes

Implementing subtype of a subtype in type/subtype design pattern with mutually exclusive subclasses

According to my interpretation of your specifications, you want to find a method to implement two different (but connected) supertype-subtype structures. Dr. E. F. Codd —the originator of the ...
MDCCL's user avatar
  • 8,520
6 votes
Accepted

Many to many between lots of tables?

Create a supertype entity CarBody for subtypes Sedan, SUV, ... and move all general information to that supertype. Your M-M relationship will be between CarBody and Feature. See this topic for ...
Serg's user avatar
  • 1,266
4 votes
Accepted

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

Ultimately, I implemented ypercube's suggestion from comments: The "type" column can be defined as a computed (but constant) column in SQL-Server. I think it has to be PERSISTED though so it can ...
shannon's user avatar
  • 406
4 votes

Best design to reference multiple tables from single column?

What you are describing is, at least in part, a product catalog. You have several attributes which are common to all products. These belong in a well normalized table. Beyond that, you have a ...
Joel Brown's user avatar
  • 12.5k
4 votes

Best design to reference multiple tables from single column?

Here's my problem: different brands of clothing require differing information. What is the best practice for dealing with a problem like this? Using JSON and PostgreSQL I think you're making this ...
Evan Carroll's user avatar
  • 63.6k
4 votes

Abstract classes in SQL Server. Are they even possible?

There is nothing like this in SQL Server. What tool are you building and maintaining your schema in though, will you be using database projects? If you went the "manual" method, while there is no ...
Cody Konior's user avatar
  • 3,508
4 votes
Accepted

Reviewing the design of a database about animals of two distinct species

Analyzing the scenario from a pure conceptual perspective, an important entity type that has been missrepresented so far is Animal, which would help to (a) convey all the relevant associations more ...
MDCCL's user avatar
  • 8,520
3 votes

Basic RDBMS table relations

There is not really much to talk about here. At least, the conventional wisdom is very clear. Only option 2 should be considered unless you have a very, very good reason, borne out of practical ...
Joel Brown's user avatar
  • 12.5k
3 votes
Accepted

How can I enforce a one-to-zero-or-one relationship with multiple types?

You can add an attribute to MEMBER that defines the type. Example: CREATE TABLE MEMBER ( MEMBER_ID INT NOT NULL PRIMARY KEY -- ID is to vague IMO , MEMBER_TYPE CHAR(1) NOT NULL , ... , CONSTRAINT <...
Lennart - Slava Ukraini's user avatar
3 votes
Accepted

Internationalization of a Supertype-subtype

I personally hate having to join multiple tables to get the international variant of some text value so I make each attribute that needs to be internationalized either an XML column or a JSON column ...
Mr.Brownstone's user avatar
3 votes
Accepted

What is the correct way to enforce unique "IS-A" relationship?

Relation Application that refer to the Person and Position relations should have unique personID attribute and should have the single attribute positionID that can have one and only one value at once.
Kondybas's user avatar
  • 4,448
3 votes

Dealing with subtypes and supertypes in Database design

Not sure I understand all of your questions, but one fairly common model for super/sub-type is to add a classifier attribute to your supertype: CREATE TABLE super ( xxx_id ... not null primary key , ...
Lennart - Slava Ukraini's user avatar
2 votes

How to represent the optionality of a many-to-one mandatory relationship from the Parent to its two Child entity types in an ERD?

It looks to me as though child_1 and child_2 are two instances of a single entity type. If so, then you have a (second) relationship such that Every parent must have one and only one favourite ...
Hugh Lapham's user avatar
2 votes

Modeling a Car Dealership context in an Entity-Relationship diagram

Ok, so create a Table named: Service_Work with Service_id as unique identifier or primary_key constraint; An object inside this table is named, Inspections_Type with a foreign_key referencing the ...
Database Dimas USA's user avatar
2 votes

Using table inheritance instead of mapping tables

Many reasons to avoid, and all boil down to violating the principles of the relational model. Most obviously, the child tables are bags, not relations, and the lack of foreign keys violate the ...
Leandro's user avatar
  • 196
2 votes

One-to-"Zero or One" Relation

Let me suggest a third alternative: You can use triggers to check your business rules and fail the insert/update in a similar fashion a foreign key constraint would. For your scenario I setup ...
tomfern's user avatar
  • 109
2 votes
Accepted

One-to-"Zero or One" Relation

Although very many NULLable columns are normally a sign of bad design, in some instances, it's perfectly legit to have them, and use the database CONSTRAINTS to make sure that the 0-1 relations and ...
joanolo's user avatar
  • 13.5k
2 votes
Accepted

Abstract classes in SQL Server. Are they even possible?

While correct that PostgreSQL does indeed support this request, SQL Server has no similar ability. That leaves you with either placing all of the "common" columns in each table, or having a "base" ...
Solomon Rutzky's user avatar
2 votes

Can a weak entity type be involved in an "is-a" relationship?

When I've modeled databases, I would use a isType description where you have isA. You appear to have two types of Specs; labelSpecs and itemSpecs. Common data belongs in Specs entity, with type ...
BillThor's user avatar
  • 4,468
2 votes
Accepted

Is it possible to represent a ‘mutual IS-A’ relationship in an entity-relationship diagram?

In databases, subtyping means that one domain is a subset of another. If A is a subset of B and B is a subset of A, then it means A = B. The same logic applies whether A and B are entity sets or ...
reaanb's user avatar
  • 393

Only top scored, non community-wiki answers of a minimum length are eligible