To use Polymorphic or not to use?

tdlr: polymorphic associations could solve my problem, but it doesnt feel correct, please help.

I have a project where products can be purchased. Each product has a type and requires different settings/properties for each type. Its not product attributes for variants but more like product meta. For instance there could be a product of type booking, so when a product of type booking is created I need to store such properties as; capacity, format (online, in-studio) etc. Now a product of type 'event' I need to store such settings as; venue_location, num_tickets_available etc. I could add more product types in future such as product bundles, grouped products, like woocommerce. Each product no matter the type would have common fields such as title, slug, description etc.

One important point, is all meta for a product type is fixed, its not dynamic like custom fields.

Now I could store all of these columns on the products table with a column 'product_type_id' to denote product type, and then only populate specific columns as needed, but I would have a ton of null fields.

Using polymorphic associations, you could have a products table with productable_type, productable_id and common fields to all products such as title, slug, etc. Then have a booking_product_meta table and a event_product_meta table. So productable_type would point to either of these models for their product meta data.

So polymorphic associations could solve this, but I don't like the fact I'm unable to enforce data consistency on the database level using foreign keys. Another reason I dont feel like polymorphic associations is right for this, is that i would have to create the product meta in thier respective table(s) before creating the product iself in the polymorphic table, that feels wrong. Also common examples of polymorphic associations have a Post, Video and then each Post and Video can both have comments so you have a polymorphic comments table. If you delete a comment then Post or Video would remain as it is it's own entity so to speak, but in my scenario the Product would be the main entity.

At the end of the day I need to be able to easily query for all products, search by slug AND product meta, like get all products of type booking that have format === 'online' etc, I also need to add a Product entity to the cart, and have all order_line_items point to a Product entity.

Another solution is EAV for the meta, but all i read online is to stay away from EAV, as its nightmare long-term and querying meta on an EAV table is very slow.

I am super stuck and at a loss as to how best model this, any help would be great.

LaryAI

3 years ago

Level 58

Based on the requirements mentioned in the question, using polymorphic associations seems like a reasonable solution. However, if the concern is about data consistency and foreign keys, one possible solution could be to use a combination of polymorphic associations and database triggers.

Here's how it could work:

Create a products table with common fields such as title, slug, description, etc.
Create a product_meta table with columns for each type of product meta data. For example, for a booking product, you could have columns like capacity, format, etc. For an event product, you could have columns like venue_location, num_tickets_available, etc.
Create a product_meta_types table with columns for each type of product meta data. For example, for a booking product, you could have a row with name set to "booking". For an event product, you could have a row with name set to "event".
Create a product_product_meta table with columns for product_id, product_meta_id, and product_meta_type_id. This table will be used to associate a product with its meta data.
Create a trigger on the product_product_meta table that ensures that the product_meta_type_id matches the product_type_id of the associated product. This will enforce data consistency at the database level.

Here's an example of how the tables could be created:

CREATE TABLE products (
    id INT PRIMARY KEY,
    title VARCHAR(255),
    slug VARCHAR(255),
    description TEXT,
    product_type_id INT
);

CREATE TABLE product_meta (
    id INT PRIMARY KEY,
    capacity INT,
    format VARCHAR(255),
    venue_location VARCHAR(255),
    num_tickets_available INT
);

CREATE TABLE product_meta_types (
    id INT PRIMARY KEY,
    name VARCHAR(255)
);

INSERT INTO product_meta_types (name) VALUES ('booking'), ('event');

CREATE TABLE product_product_meta (
    id INT PRIMARY KEY,
    product_id INT,
    product_meta_id INT,
    product_meta_type_id INT,
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (product_meta_id) REFERENCES product_meta(id),
    FOREIGN KEY (product_meta_type_id) REFERENCES product_meta_types(id)
);

CREATE TRIGGER product_product_meta_type_check
BEFORE INSERT ON product_product_meta
FOR EACH ROW
BEGIN
    DECLARE product_type_id INT;
    SELECT product_type_id INTO product_type_id FROM products WHERE id = NEW.product_id;
    IF product_type_id != NEW.product_meta_type_id THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Product meta type does not match product type';
    END IF;
END;

With this setup, you can easily query for all products and their meta data using joins:

SELECT products.*, product_meta.*
FROM products
JOIN product_product_meta ON products.id = product_product_meta.product_id
JOIN product_meta ON product_product_meta.product_meta_id = product_meta.id
WHERE products.slug = 'some-slug'
AND product_meta.format = 'online'
AND product_meta_types.name = 'booking';

And you can add a product to the cart and have order line items point to it using the product_id column in the order_line_items table.

Please or to participate in this conversation.