CODEWITHSUNDEEP
sqlpostgresqldatabase-designdatabase-normalizationdbml
Virgilio Gonzenbach
Virgilio Gonzenbach

Reputation: 1

Normalization in PostgreSQL - share a primary key across tables?

I'm designing a PostgreSQL database that involves three tables: buys, sells, and transaction_pairs. In each of the buys and sells tables I have a unique identifier column id.

database schema

In the pairs table, I have buy_id and sell_id columns which act as foreign keys to the id fields of the buys and sells tables respectively. The transaction_pairs table:

id filing_year buy_id sell_id
1 2020 1 1
2 2020 2 2
3 2020 3 3
4 2020 4 4

The id values in the pairs table correspond to the same id values in the buys and sells tables. Therefore I'm considering whether I can eliminate redundancy by sharing a primary key across all three tables.

(The reason these numbers match is I'm splitting the original transactions to pair buys with sells. Hence the one-to-many relationship(s) with the raw_transactions table and the three buys, sells and transaction_pairs having the same number of rows.)

I'm trying to avoid merging all of the information into a single table if possible, but I also want to avoid duplicating information.

Is it good practice to share a primary key across multiple tables in this way, and if so, how should I implement this in PostgreSQL?

I'm also interested in how to document this kind of relationship in DBML.

To reduce redundancies, I tried creating a composite key in the transaction_pairs table:

CREATE TABLE "transaction_pairs" (
  "buy_id" INT,
  "sell_id" INT,
  "filing_year" INT,
  PRIMARY KEY ("buy_id", "sell_id")
);

However, there is a remaining redundancy in that buy_id and sell_id will always have the same value.

Can we reduce it further?

Upvotes: -2

Views: 133

Answers (1)

Huzaifa
Huzaifa

Reputation: 425

Currently, you have two identical tables (buy and sells). You can normalise it further, you can have a single primary key, being referenced as foreign key in all other tables.

For instance: Have another table having common information of "buy" and "sells" table. Each row in this table will have unique id(primary key), which will be a foreign key in other tables.

Also: 100% database normalisation is not achievable.

Upvotes: 0

Related Questions