-2

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?

philipxy
  • 14,867
  • 6
  • 39
  • 83
Virgilio Gonzenbach
  • 1
  • 1
    Why not have the `transaction_pairs` have the `buy_id` and `sell_id` be FK's to the `raw_transactions` table? – Adrian Klaver Jun 26 '23 at 23:40
  • Because in my use case (crypto tax reporting), I need to split raw transactions further. For example, suppose you buy 2 BTC at two different prices (1) $30k , and (2) $60k. Then, you sell 0.5 BTC @ 45k (3) totaling 3 raw transactions. To optimize for losses, the sell transaction will be reported identically in `sells` but the paired buy will come from the second `raw_transaction` @ 60k and it will only represent 0.5 of the total amount bought [to show a loss of 0.5 * (60k - 45k)]. Hence the `transaction_pairs` refer to the `buys` and `sells` table and not `raw_transactions`. – Virgilio Gonzenbach Jun 27 '23 at 08:32
  • And I can have situations where the sell is split too. In general, there will be many more rows in `buys` and `sells` than in `raw_transactions`. – Virgilio Gonzenbach Jun 27 '23 at 09:00

1 Answers1

0

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.

Huzaifa
  • 484
  • 4
  • 8
  • Hmm I could make a primary key in `transaction_pairs` then reference it as a foreign key in `buys` and `sells`. My follow up question would be if it is okay for a table (e.g. `buys`) to have no primary key (?) – Virgilio Gonzenbach Jun 27 '23 at 08:37