CODEWITHSUNDEEP
sqlpostgresqldatabase-design
Stefan Falk
Stefan Falk

Reputation: 25387

Should I use a compound primary key here or is UNIQUE(fk1, fk2) a better design?

I am not very experienced with SQL and therefore I have troubles when it comes to decision making from time to time. What I am having are the tables shop, language and shop_supported_language. 

CREATE TABLE shop (
    -- PRIMARY KEY  
    id BIGSERIAL PRIMARY KEY
);

CREATE TABLE language (
    -- PRIMARY KEY  
    id BIGSERIAL PRIMARY KEY,

    -- ATTRIBUTES   
    code CHAR(5) NOT NULL,
    UNIQUE (code)   
);

CREATE TABLE shop_supported_language (

    -- PRIMARY KEY  
    id BIGSERIAL PRIMARY KEY,

    -- FOREIGN KEYS

    shop_id BIGINT NOT NULL,

    CONSTRAINT fk__shop_supported_language__shop
        FOREIGN KEY (shop_id)
        REFERENCES shop(id),

    language_id BIGINT NOT NULL,

    CONSTRAINT fk__shop_supported_language__language
        FOREIGN KEY (language_id)
        REFERENCES language(id) 

    -- Would definitely not hurt:
    -- UNIQUE(shop_id, language_id)
);

As the name suggests, shop_supported_language represents the languages that a shop is actually supporting. A shop can decide in what translations it wants to provide e.g.

CREATE TABLE shop_offer_details (

    -- PRIMARY KEY  
    id BIGSERIAL PRIMARY KEY,

    -- FOREIGN KEYS 
    shop_offer_id BIGINT NOT NULL,  
    -- ...

    shop_supported_language_id BIGINT NOT NULL,

    CONSTRAINT fk__shop_offer_details__shop_supported_language
        FOREIGN KEY (shop_supported_language_id)
        REFERENCES shop_supported_language(id),

    -- ...
);

Is it a good idea to not have a compound primary key for shop_supported_language? Imho it would make sense to set a UNIQUE constraint to shop_id and language_id and I'm good.

Since this is a core element in my database I would like to know if what I am doing is basically okay or fundamentally dangerous.

The reason why I don't want to use that compound PK is actually shop_offer_details. In that case I don't have to reference a compound PK. Would that be an argument against the compound PK?

Upvotes: 0

Views: 623

Answers (2)

philipxy
philipxy

Reputation: 15118

There seem to be three (candidate and/or super-) key issues muddled here: PRIMARY KEY vs UNIQUE NOT NULL keys, simple vs composite keys and natural vs surrogate keys.

A PRIMARY KEY constraint is just one of whatever UNIQUE NOT NULL constraints you have that you decided to call "primary". There's no constraint difference between declaring (FK1, FK2) a PRIMARY KEY or a UNIQUE NOT NULL key. There might be project heuristics about when to use one over the other (eg declaring a key as PRIMARY KEY when it's the one to be used as FOREIGN KEY when you have a choice or having small values as keys for performance) and the DBMS might treat it differently (eg indexing it by default). There's no role for PRIMARY KEYs in relational theory.

There is also nothing wrong per se with composite keys. There are reasons for adding a simple surrogate candidate key then also using it as foreign key. Some of which are re implementation and need to be shown worth-while. And some of which are for good logical design, eg when a distinguishing id must be introduced after the system starts up or to sheild from changes in what columns are available for natural keys.

Upvotes: 1

amui
amui

Reputation: 51

If shop table has a shop_id that uniquely identifies the shop and if language table has a language_id that uniquely identifies the language and the relationship between those 2 tables is that a shop can support many languages and a language can be supported by many shops (many to many), then it should be fine to use shop_id and language_id as the 2 keys to identify what shop support what language and what language is used by what shops. I don't see a need to generate another key to identify the many to many relationship (shop_id and language_id).

Upvotes: 1

Related Questions