0

Users table

ID COLUMN_1 COLUMN_2 COLUMN_3 ETC...
348 ... ... ... ...

Ads table

ID USER_ID COLUMN_1 COLUMN_2 COLUMN_3 ETC...
91 348 ... ... ... ...

Transactions table

COST NUMBER_OF_DAYS TRANSACTION_DATE TYPE AD_ID USER_ID
3000 NULL ... 1 NULL 348
-800 30 ... 2 NULL 348
-50 NULL ... 3 91 348
-300 19 ... 4 91 348

There are 4 types of transactions

  • 1 = When the user adds money to his wallet.
  • 2 = When the user buys a gold membership.
  • 3 = When the user reposts his ad. (The ad will appear again on the top of the list)
  • 4 = When the user makes his ad a featured ad. (The ad will appear top of the non-featured ads)

I don't like designing the transactions table in this way because there are columns not required according to type value, Is there any better way to design the transactions table?

Taha Sami
  • 1,565
  • 1
  • 16
  • 43
  • Question is likely to attract opinion based responses. Mine is I prefer to keep all transactions in 1 place and live with redundancy. – P.Salmon Nov 22 '22 at 09:00
  • @P.Salmon I have a notifications table also with 28 types and the notifications table is designed like the transactions table, Do you think the design of my tables is good? – Taha Sami Nov 22 '22 at 09:13
  • "Good" is by definition opinion based. How should we evaluate "good"? What are the characteristics of a "good" design in your case? – Neville Kuyt Nov 22 '22 at 09:17
  • @NevilleKuyt Everything is working well on the client side but I'm asking here because maybe there is a better way to design the table than the current way, I hate this design only because according to the type value some columns will be used and some will not. I think NoSQL will solve this problem. – Taha Sami Nov 22 '22 at 09:26

1 Answers1

1

This is an example of mapping inheritance relationships ("there are 4 types of transaction") to the relational database model. TL;DR: there are no elegant solutions - you have to choose the kind of inelegance you want. There are 3 common approaches.

Your design is an example of "single table inheritance". It's the simplest to work with - the other options involve adding tables to the design, with the need to join - but it requires columns that aren't always populated. It also pushes the validation of the object to the application layer - you can't create DDL to ensure that AD_ID is not null for entities of type 1 and 2.

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52