sql - How to design a table which is referenced by two other tables in postgres - Stack Overflow

admin管理员组

文章数量:1122832

I have a table named urls:

id	name
1	url1
2	url2

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.

• sql
• postgresql

Share Improve this question Follow edited yesterday Dale K 27k1515 gold badges5454 silver badges8282 bronze badges asked yesterday hafishafis 1271111 bronze badges 9

• 1 You need to have an empty line before and after your table markdown to make it show up properly – marc_s Commented yesterday
• i was confused why it was not shown properly, thanks – hafis Commented yesterday
• If your documents and doctypes tables need to have more than one URL, and a single URL can be related to multiple documents or doctypes, this approach is corrent. (ManyToMany). – ABDULLOKH MUKHAMMADJONOV Commented yesterday
• a single url is not connected to multiple document or doctypes. each url is only connected to one doctypes or documents. and now i am not sure each documents have multiple urls. how can i design in either of the case – hafis Commented yesterday
• You should become clear about the requirements first. – Laurenz Albe Commented yesterday

 |  Show 4 more comments

2 Answers 2

Sorted by: Reset to default 1

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!)

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, then remove the urls and add a url table instead:

CREATE TABLE urls
(
  id          BIGINT       GENERATED ALWAYS AS IDENTITY,
  url         VARCHAR(100) NOT NULL UNIQUE,
  id_doctype  BIGINT       REFERENCES doctypes(id),
  id_document BIGINT       REFERENCES documents(id),
  CONSTRAINT chk_url_one_reference
    CHECK((id_doctype IS NULL     AND id_document IS NOT NULL) OR 
          (id_doctype IS NOT NULL AND id_document IS NULL))
);

本文标签： sqlHow to design a table which is referenced by two other tables in postgresStack Overflow