PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to design a table which is referenced by two other tables in postgres

I have a table named urls:

id name
1 url1
2 url2

This urls table is referenced by two other tables documents and doctypes.

documents:

id reference
1 kl/dkj/192
2 kl/dji/982

doctypes:

id name
1 document1
2 document2

documents and doctypes have other columns also. I want to add urls for both documents and doctypes table. My requirement is to know how can I establish a relationship between urls and these tables. I think when I list document and doctypes in my app, I don’t want to display urls, but when I click on detailed view, I can join document and urls tables for knowing url value.

Now I designed relationship like this – document_urls:

document_id url_id
1 3
2 2

doctype_urls:

doctype_id url_id
1 3
2 2

My question: is this a good approach?

I also think I could have altered documents table to reference urls table like this

id reference url_id
1 kl/djkf/34 1
2 kl/de/3445 3

For this approach my question: is it a good approach, since url column is not a required column, and I was not listing url details when I list my documents.

Tags:

2

Answers


  1. 0

    The easiest solution would be to put the urls in the tables:

    CREATE TABLE doctypes
(
  id    BIGINT       GENERATED ALWAYS AS IDENTITY,
  name  VARCHAR(100) NOT NULL,
  url   VARCHAR(100) NOT NULL
);

CREATE TABLE documents
(
  id         BIGINT       GENERATED ALWAYS AS IDENTITY,
  id_doctype BIGINT       NOT NULL REFERENCES doctypes(id),
  reference  VARCHAR(100) NOT NULL,
  url        VARCHAR(100)
);

    You can even add unique constraints on the urls:

    ALTER TABLE doctype ADD CONSTRAINT unique_doctype_url UNIQUE (url);
ALTER TABLE documents ADD CONSTRAINT unique_doctype_url UNIQUE (url);

    The only thing that this does not do is guarantee that there is no url used both for a doctype and a document at the same time. If you must guarantee that, this becomes much more complicated:

    -- A table for the type of a url: 1 for doctype, 2 for document
CREATE TABLE urltypes
(
  id    BIGINT       GENERATED ALWAYS AS IDENTITY,
  name  VARCHAR(100) NOT NULL UNIQUE
);

-- A table with all urls, each unique, plus a
-- secondary unique constraint on id + type, so
-- we can then guarantee that a document gets a
-- document url and a doctype gets a doctype url.
CREATE TABLE urls
(
  id         BIGINT       GENERATED ALWAYS AS IDENTITY,
  id_urltype BIGINT       NOT NULL REFERENCES urltypes(id),
  url        VARCHAR(100) NOT NULL UNIQUE,
  UNIQUE (id, id_urltype)
);

-- The doctype table with a doctype url
CREATE TABLE doctypes
(
  id         BIGINT       GENERATED ALWAYS AS IDENTITY,
  name       VARCHAR(100) NOT NULL,
  id_url     BIGINT       NOT NULL,
  id_urltype BIGINT       NOT NULL,
  CONSTRAINT chk_doctype_urltype CHECK(id_urltype = 1),
  CONSTRAINT fk_doctype_url
    FOREIGN KEY (id_url, id_urltype) 
    REFERENCES urls (id, id_urltype)
);

-- The document table with a document url
CREATE TABLE documents
(
  id         BIGINT       GENERATED ALWAYS AS IDENTITY,
  id_doctype BIGINT       NOT NULL REFERENCES doctypes(id),
  reference  VARCHAR(100) NOT NULL,
  id_url     BIGINT       NOT NULL,
  id_urltype BIGINT       NOT NULL,
  CONSTRAINT chk_document_urltype CHECK(id_urltype = 2),
  CONSTRAINT fk_doctype_url
    FOREIGN KEY (id_url, id_urltype) 
    REFERENCES urls (id, id_urltype)
);
    Login or Signup to reply.
  2. 0

    The only way to determine the best design for a database schema is to analyse the entities and relationships you want to represent.

    Firstly, be careful of confusing data types with entities – just because many things have an attribute called "URL", with similar-looking content, does not necessarily mean that they should reference a URL entity. As an example, imagine having a "users" table and a "products" table; both might have a "name" attribute, but it would be very unusual to consider "name" an entity on its own, and have a single table called "names", because "user name" and "product name" don’t have any natural connection.

    If the "document URL" is actually always added to a fixed prefix of "/documents/", and a "doctype URL" is always added to a fixed prefix of "/doctypes/", they can be represented as separate entities. If you want to represent a single pool of URLs, which can be assigned ad hoc for various different purposes, you need a separate "URL" entity.

    Secondly, you need to determine the "cardinality" of the relationships. Can one document have zero URLs, multiple URLs, or always exactly one? Can one URL refer to zero documents, multiple documents, or always exactly one?

    With the answers to these questions in mind, you can choose between a number of schemas:

    • If the relationships are "each document has exactly one document_url", and "each doctype has exactly one doctype_url", you can have a non-nullable "url" column on "documents" and "doctypes"
    • If one or both relationships are "… can have zero or one …", you can make the column nullable
    • If you decide there is a separate URL entity, you need an extra table and extra constraints:
      • A foreign key from "urls" to "documents" models the relationship "one URL represents exactly one document"; same for "doctypes"
      • To also represent "one document has exactly one URL", you can add a Unique Constraint on the "document_id" foreign key (and similar on "doctype_id")
      • To represent "each URL refers to either one document or one doctype", you can add a Check Constraint on the table, e.g. NOT (document_id IS NULL AND doctype_id IS NULL) (reword with care, remembering De Morgan’s laws!)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search