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, Boat and Plane (along with two more that were not made known) —the subtypes—.
I will detail below the method I would follow to manage such a scenario.
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 Inheritance: One table per concrete type, each table columns for each attributes of the type. No relation between tables.
Class Table Inheritance: One table per type, ...
The part of the scenario that you are confused with can be modeled with a classic construct called supertype-subtype1 structure.
I will (1) introduce some pertinent preliminary ideas, (2) detail how I would delineate —at the conceptual level— the business context under consideration, and (3) provide additional related material —e.g., the corresponding ...
Physical implementation of subtyping in a database is a complex issue. Unless you have a situation where it offers compelling advantages (see below for one or two examples) it adds complexity into implementation while providing relatively little value.
Having done this with really complex subtyping (applicaitons and sentences on a court case management ...
Since I made the diagram, I better answer ;)
Current relational databases unfortunately don't support the inheritance directly, therefore you need to transform it into "plain" tables. There are generally 3 strategies for doing so:
All classes1 in a single table with NULL-able non-common fields.
Concrete classes2 in separate tables. Abstract classes don't ...
The proper structure for this scenario is a SubClass / Inheritance model, and is nearly identical to the concept I proposed in this answer: Heterogeneous ordered list of value.
The model proposed in this question is actually quite close in that the Animal entity contains the type (i.e. race) and the properties that are common across all types. However, ...
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 entity types, inspecting their properties and associations before (b) thinking in terms of tables, columns and constraints —aspects that correspond to the ...
If I understand your specifications properly, your scenario involves —among other significant aspects— a supertype-subtype structure.
I will exemplify below how to (1) model it at the conceptual level of abstraction and subsequently (2) represent it in a logical-level DDL design.
The following conceptual formulations are among the most ...
The way to manage this is that your sub-types have to be determined by the super-type (i.e. the PK of the sub-type is also a FK from the sub-type to the super-type.)
The challenge is understanding whether something is truly mutually exclusive or not. The attributes of sub-types should apply only to those sub-types, but it may well be that some sub-types ...
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 used by the end-user.
Concrete Technical Drawbacks
Drawbacks on UNIQUE and REFERENCES
The docs covers some of the drawbacks in the CAVEAT section (below is ...
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 table schema:
CREATE TABLE #Brands
BrandId int NOT NULL PRIMARY KEY,
BrandName nvarchar(100) NOT NULL
CREATE TABLE #Clothes
ClothesId int NOT NULL ...
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:
CREATE TABLE Contact
( ID INT NOT NULL IDENTITY,
Type VARCHAR(10) NOT NULL,
PRIMARY KEY (ID, Type)
CREATE TABLE Address
( ID INT NOT ...
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..* relationship to a ticket, and make service, inspection, and repair inherit from it (1..1). In many cases you will probably just have Work with the Type being one of ...
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 saving 3 bytes in every row, in all 3 tables and in every index that includes ClassTypeID - which would be every index on these tables, if ClassTypeID is part of ...
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 transform variable entity into relational table, I would ask: what exactly are you trying to optimize? Storage? The object model? Query complexity? Query ...
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 Relational Paradigm— introduces and discusses relationships of this kind in his 1979 paper entitled Extending the Database Relational Model to Capture More Meaning.
The motivation for piling every kind of address into a single table is usually a misinterpretation and misapplication of the notion of code reuse.
People can make the mistake of assuming that because you have two entities with some common set of attributes, that those attributes belong in their own table. Sometimes entities have similar or identical ...
There is no need for distinct Civil_ID and Worker_ID; just continue to use Person-ID as the key for all three tables: Person, Civil, and Worker. Add a column PersonType to Person with the two values "Civil" and "Worker".
This now represents the two sub-classes CivilClass and WorkerClass of the abstract base class PersonClass as sub-entities Civil and ...
I think you can make an Orders entity that is a relationship between Products and Customers. The Orders would have subclasses FoodOrders and ShoeOrders, which respectively would be the relationships between the subclasses of Food - GroceryStores and Shoes - ShoeStores.
Your case is an instance of class/subclass modeling. Or, as you have diagrammed it in ER, generalization/specialization.
There are three techniques that will help you design mysql tables to cover this case. They are called Single Table Inheritance, Class Table Inheritance, and Shared Primary key. You can read up on them in the info tab from the ...
I have a person table.
Fine (just changed the name of the PK column):
and a user table as a child. They must be 1:1 relationship, because an user cannot be repeated twice.
If the relationship is 1:1 (assuming that person is the supertpe and user is the subtype, you can handle ...
First off, you are doing well to distinguish between ER modeling and relational modeling. Many newbies don't.
Here are some buzzwords you can use to look up helpful articles on the web.
Your case is a classic case of class/subclass or, if you like, type/subtype.
The phrase that's used in ER modeling is "generalization/specialization". And many of the ...
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 participate in the Foreign Key constraint.
This worked well both for performance and compatibility with with my tools (Entity Framework <= 6.0):
CREATE TABLE ...
The Answer by MDCCL is fascinating, educational, and presumably correct (though above my pay-grade).
In contrast, I re-interpreted the Question and went back to basics for the simplest possible solution. Perhaps I am cheating and not truly answering the Question… but here goes anyways.
I was confused when reading and re-reading the Question. When seeing ...
I can't point to anything authoritative, but if you think through the details of such an implementation, the potential down sides are evident.
Having a "shared" subsidiary table is problematic primarily because if two "parent" tables (say, Customer and Employee) both utilize the same Address table to store addresses, you can't use a foreign key constraint ...
Since Consumers and Merchants share some common data as Users they should be in a single base table, as you have already mentioned.
An EAV implementation can be successful under some well planned and controlled use. But I see nothing in your question that would imply that an EAV solution is the right answer.
In fact, as you move further in your ...
This is a case of what's called "generalization/specialization" in ER lingo. It's really the same thing as what object models call "superclass/subclass" as you have done. There are two separable issues. The first is how do you want to draw the diagram, and the second is how do you want to design the tables.
As far as drawing the diagram goes, I would ...
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 series of attributes which are brand specific (and I expect could be product specific). What does your system need to do with these specific attributes? Do you ...
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 subtyping in datamodeling Supertype/Subtype deciding between category: complete disjoint or incomplete overlapping .
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 SQL specific templating for this in SSDT, you might be able to use T4 templates to do something similar.
If that interested you then another alternative for ...