CODEWITHSUNDEEP
sqldatabasepostgresqldatabase-designconstraints
James Parker
James Parker

Reputation: 1

Constraint to table column name (postgresql)

I'm implementing a product database using the single table inheritance (potentially later class table inheritance) model for product attributes. That's all working fine and well but I'm trying to figure out how best to deal with product variants whilst maintaining referential integrity.

Right now a simplified version of my main product table looks like this:

CREATE TABLE product (

    id     SERIAL         NOT NULL,
    name   VARCHAR(100)   NOT NULL,
    brand  VARCHAR(40)    NOT NULL,
    color  VARCHAR(40)[]  NOT NULL

)

(color is an array so that all of the standard colors of any given product can be listed)

For handling variants I've considered tracking the properties on which products vary in a table called product_variant_theme:

CREATE TABLE product_variant_theme (

    id              SERIAL       NOT NULL,
    product_id      INT          NOT NULL,
    attribute_name  VARCHAR(40)  NOT NULL

)

Wherein I insert rows with the product_id in question and add the column name for the attribute into the attribute_name field e.g. 'color'.

Now feel free to tell me if this is an entirely stupid way to go about this in the first place, but I am concerned by the lack of a constraint between attribute_name and the actual column name itself. Obviously if alter the product table and remove that column I might still be left with rows in my second table that refer to it. The functional equivalent of what I'm looking for would be something like a foreign key on attribute_name to the information_schema view that describes the tables, but I don't think there's any way to do that directly, and I'm wondering if there is any reasonable way to get that kind of functionality here.

Thanks.

Upvotes: 0

Views: 744

Answers (1)

Glenn
Glenn

Reputation: 9160

Are you looking for something like this?

product
=======
id
name


attribute
=========
id
name


product_attribute_map
=====================
product_id
attribute_id
value

Upvotes: 1

Related Questions