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:
|‘commission’ or ‘payment’
Commissions have the same fields as payments, plus two additional fields: qualified and customerPaymentId
|Customer Payment ID
|boolean (presumably 0 or 1)
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.