Asked  7 Months ago    Answers:  5   Viewed   42 times

Well here's my problem I have three tables; regions, countries, states. Countries can be inside of regions, states can be inside of regions. Regions are the top of the food chain.

Now I'm adding a popular_areas table with two columns; region_id and popular_place_id. Is it possible to make popular_place_id be a foreign key to either countries OR states. I'm probably going to have to add a popular_place_type column to determine whether the id is describing a country or state either way.

 Answers

55

What you're describing is called Polymorphic Associations. That is, the "foreign key" column contains an id value that must exist in one of a set of target tables. Typically the target tables are related in some way, such as being instances of some common superclass of data. You'd also need another column along side the foreign key column, so that on each row, you can designate which target table is referenced.

CREATE TABLE popular_places (
  user_id INT NOT NULL,
  place_id INT NOT NULL,
  place_type VARCHAR(10) -- either 'states' or 'countries'
  -- foreign key is not possible
);

There's no way to model Polymorphic Associations using SQL constraints. A foreign key constraint always references one target table.

Polymorphic Associations are supported by frameworks such as Rails and Hibernate. But they explicitly say that you must disable SQL constraints to use this feature. Instead, the application or framework must do equivalent work to ensure that the reference is satisfied. That is, the value in the foreign key is present in one of the possible target tables.

Polymorphic Associations are weak with respect to enforcing database consistency. The data integrity depends on all clients accessing the database with the same referential integrity logic enforced, and also the enforcement must be bug-free.

Here are some alternative solutions that do take advantage of database-enforced referential integrity:

Create one extra table per target. For example popular_states and popular_countries, which reference states and countries respectively. Each of these "popular" tables also reference the user's profile.

CREATE TABLE popular_states (
  state_id INT NOT NULL,
  user_id  INT NOT NULL,
  PRIMARY KEY(state_id, user_id),
  FOREIGN KEY (state_id) REFERENCES states(state_id),
  FOREIGN KEY (user_id) REFERENCES users(user_id),
);

CREATE TABLE popular_countries (
  country_id INT NOT NULL,
  user_id    INT NOT NULL,
  PRIMARY KEY(country_id, user_id),
  FOREIGN KEY (country_id) REFERENCES countries(country_id),
  FOREIGN KEY (user_id) REFERENCES users(user_id),
);

This does mean that to get all of a user's popular favorite places you need to query both of these tables. But it means you can rely on the database to enforce consistency.

Create a places table as a supertable. As Abie mentions, a second alternative is that your popular places reference a table like places, which is a parent to both states and countries. That is, both states and countries also have a foreign key to places (you can even make this foreign key also be the primary key of states and countries).

CREATE TABLE popular_areas (
  user_id INT NOT NULL,
  place_id INT NOT NULL,
  PRIMARY KEY (user_id, place_id),
  FOREIGN KEY (place_id) REFERENCES places(place_id)
);

CREATE TABLE states (
  state_id INT NOT NULL PRIMARY KEY,
  FOREIGN KEY (state_id) REFERENCES places(place_id)
);

CREATE TABLE countries (
  country_id INT NOT NULL PRIMARY KEY,
  FOREIGN KEY (country_id) REFERENCES places(place_id)
);

Use two columns. Instead of one column that may reference either of two target tables, use two columns. These two columns may be NULL; in fact only one of them should be non-NULL.

CREATE TABLE popular_areas (
  place_id SERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  state_id INT,
  country_id INT,
  CONSTRAINT UNIQUE (user_id, state_id, country_id), -- UNIQUE permits NULLs
  CONSTRAINT CHECK (state_id IS NOT NULL OR country_id IS NOT NULL),
  FOREIGN KEY (state_id) REFERENCES places(place_id),
  FOREIGN KEY (country_id) REFERENCES places(place_id)
);

In terms of relational theory, Polymorphic Associations violates First Normal Form, because the popular_place_id is in effect a column with two meanings: it's either a state or a country. You wouldn't store a person's age and their phone_number in a single column, and for the same reason you shouldn't store both state_id and country_id in a single column. The fact that these two attributes have compatible data types is coincidental; they still signify different logical entities.

Polymorphic Associations also violates Third Normal Form, because the meaning of the column depends on the extra column which names the table to which the foreign key refers. In Third Normal Form, an attribute in a table must depend only on the primary key of that table.


Re comment from @SavasVedova:

I'm not sure I follow your description without seeing the table definitions or an example query, but it sounds like you simply have multiple Filters tables, each containing a foreign key that references a central Products table.

CREATE TABLE Products (
  product_id INT PRIMARY KEY
);

CREATE TABLE FiltersType1 (
  filter_id INT PRIMARY KEY,
  product_id INT NOT NULL,
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

CREATE TABLE FiltersType2 (
  filter_id INT  PRIMARY KEY,
  product_id INT NOT NULL,
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

...and other filter tables...

Joining the products to a specific type of filter is easy if you know which type you want to join to:

SELECT * FROM Products
INNER JOIN FiltersType2 USING (product_id)

If you want the filter type to be dynamic, you must write application code to construct the SQL query. SQL requires that the table be specified and fixed at the time you write the query. You can't make the joined table be chosen dynamically based on the values found in individual rows of Products.

The only other option is to join to all filter tables using outer joins. Those that have no matching product_id will just be returned as a single row of nulls. But you still have to hardcode all the joined tables, and if you add new filter tables, you have to update your code.

SELECT * FROM Products
LEFT OUTER JOIN FiltersType1 USING (product_id)
LEFT OUTER JOIN FiltersType2 USING (product_id)
LEFT OUTER JOIN FiltersType3 USING (product_id)
...

Another way to join to all filter tables is to do it serially:

SELECT * FROM Product
INNER JOIN FiltersType1 USING (product_id)
UNION ALL
SELECT * FROM Products
INNER JOIN FiltersType2 USING (product_id)
UNION ALL
SELECT * FROM Products
INNER JOIN FiltersType3 USING (product_id)
...

But this format still requires you to write references to all tables. There's no getting around that.

Tuesday, June 1, 2021
 
rasmusx
answered 7 Months ago
97

No, a foreign key constraint always references exactly one parent table.

This question comes up frequently. Here are some of my past answers to it:

  • Why can you not have a foreign key in a polymorphic association?
  • Possible to do a MySQL foreign key to one of two possible tables?
  • Referencing foreign keys in the same column
  • In a StackOverflow clone, what relationship should a Comments table have to Questions and Answers?
  • MySQL - Conditional Foreign Key Constraints
  • How to handle an β€œOR” relationship in an ERD (table) design?
  • MySQL: Two n:1 relations, but not both at once

For more on Polymorphic Associations, see my presentation Practical Object-Oriented Models in SQL or my book, SQL Antipatterns: Avoiding the Pitfalls of Database Programming.

Saturday, June 19, 2021
 
Sabya
answered 6 Months ago
96

Yep. That's totally possible.

You might need to specify the class name for header_image, as it can't be inferred. Include :dependent => :destroy too, to ensure that the images are destroyed if the article is removed

class Article < ActiveRecord::Base
  has_one :header_image, :as => :imageable, :class_name => 'Image', :dependent => :destroy
  has_many :images, :as => :imageable, :dependent => :destroy
end

then on the other end...

class Image < ActiveRecord::Base
  belongs_to :imageable, :polymorphic => true
end
Monday, August 9, 2021
 
lewiguez
answered 4 Months ago
99

It seems that I was trying to design the class hierarchy on the wrong entities. My understanding was that I had a lot of "GenericChildTables" (like Documents) that should point to a (non-existing) entity that would have a composite key ObjectType+ObjectID. And then I was trying to create that new entity (let's call it "BusinessObject") and map my core entities (Students, Teachers, etc) to be subtypes of this BusinessObject.

Then I saw Gert Ardold's question and realized that the correct inheritance design was NOT about grouping Students/Teachers/etc into a supertype, but about splitting those GenericChildTables into multiple subtypes.

I'll use the Documents table as an example to show how I converted those GenericChildTables into a TPH, and how I mapped my core entities (Students, Teachers, etc) to collections of those subtypes.

First, I created the derived classes (subtypes), added navigation properties, and mapped those subtypes to the base type using ObjectType as type discriminator:

public class StudentDocument : Document
{
    public Student Student { get; set; }
    public int StudentID { get; set; } 
}
public class TeacherDocument : Document
{
    public Teacher Teacher { get; set; }
    public int TeacherID { get; set; } 
}
modelBuilder.Entity<Document>()
.Map<StudentDocument>(m => {
    m.Requires("ObjectType").HasValue("STUDENT");
})
.Map<TeacherDocument>(m => {
    m.Requires("ObjectType").HasValue("TEACHER");
});

Then I added the navigation properties to my core classes (Student and Teachers), pointing to the subtypes created:

partial class Student
{
   public virtual ICollection<StudentDocument> Documents { get; set; }
}
partial class Teacher
{
   public virtual ICollection<TeacherDocument> Documents { get; set; }
}

I created the mappings for relationships Student.Documents and Teacher.Documents. Please note that I use properties StudentID and TeacherID, but they are phisically mapped to ObjectID column:

var sl = modelBuilder.Entity<StudentDocument>();
sl.Property(t => t.StudentID).HasColumnName("ObjectID");
sl.HasRequired(t => t.Student).WithMany(t => t.Documents).HasForeignKey(d => d.StudentID);

var al = modelBuilder.Entity<TeacherDocument>();
al.Property(t => t.TeacherID).HasColumnName("ObjectID");
al.HasRequired(t => t.Teacher).WithMany(t => t.Documents).HasForeignKey(d => d.TeacherID);

Finally, I removed from the base type (Document) the property ObjectType, because it is a type discriminator, and should be used only internally (cannot be exposed on class).
I also removed from the base type ObjectID because this should be mapped only on the subtypes (mapped respectively as StudentID and TeacherID).

And everything worked like a charm!

PS: Please note that if you are using T4 templates (code first from database) they will always regenerate those properties, because templates know nothing about hiearchies, so they map Documents into a single entity with properties for every column, so you should manually exclude those properties.

Wednesday, August 11, 2021
 
Jared
answered 4 Months ago
51

You should see Generic relations.

Wednesday, August 25, 2021
 
Sasha Tsukanov
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :  
Share