0

I’m learning SQL and have a very simple question here as I want to move from NoSQL to SQL (with Postgres). I don’t know whether it’s standard/better practice in this case to have one table containing everything or whether two separate tables should be used. In NoSQL it would make sense to have them in one collection (table) in a single document, but I’m not so sure about SQL.

Essentially there are two different data types: commissions and payments. Payments have the following fields:

_id, date, amount, type (‘commission’ or ‘payment’), referrerId and balance, which would presumably translate to something like this in SQL:

ID Date Amount Type Referrer ID Balance
number date number ‘commission’ or ‘payment’ number number

Commissions have the same fields as payments, plus two additional fields: qualified and customerPaymentId

Qualified Customer Payment ID
boolean (presumably 0 or 1) number

qualified (boolean - presumably should be 0 or 1) customerPaymentId

Both payments and commissions are essentially a type of ‘transaction’. Would it be better to separate payments and commissions into two different tables, or instead include them in one single table (perhaps called transactions)? The front-end will query and display both payments and commissions together in a table, and occasionally separately. Would it be expensive to query if they’re in separate tables vs a single table?

From researching other similar questions (e.g. like this one), am I right in assuming that having separate tables might be better in SQL and clearer where the relations are?

What are the common factors that should be considered when deciding whether to separate data like this? Do you consider how the data will be used, e.g. queries together or not? And other help, reading or advice would be greatly appreciated as I’m from a front-end background and want to improve my back-end knowledge. Thank you.

5
  • 1
    It all depends on the SQL statements you want to use on the data. But keeping both entities in a single table is definitely an option. Dec 14, 2022 at 4:35
  • If the SQL mostly queries the payments and commissions together, would it be better having a single table? What are the typical pros and cons of a single vs two tables? Dec 14, 2022 at 18:08
  • As I said, it all depends on the SQL statements. Without knowing them it is impossible to say what is best. Dec 15, 2022 at 3:07
  • if the payments has a relation with another table (e.g. a user table) but the commission doesn't, would that be a significant argument to separate them? Dec 16, 2022 at 18:18
  • 1
    Perhaps, but not necessarily. If you have a foreign key from another table to payments, that would be an argument for two tables. Outgoing foreign keys less so. Dec 16, 2022 at 22:38

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.