T
T
temporaryuser2020-10-28 00:35:03
PostgreSQL
temporaryuser, 2020-10-28 00:35:03

SQL query to get similar data from two tables?

Good day, I'm doing a small Pet-project and I'm having difficulties with designing a database and writing efficient SQL queries, I'll get straight to the point: I'm

using PostgreSQL, I have the following list of tables:

Pseudocode at dbdiagram.io

// Таблица с аккаунтами
Table accs {
  id int [pk, increment]
}

// Таблица с магазинами
Table shops {
  id int [pk, increment]
}

// Таблица с товарами аккаунтов
Table accs_products {
  id int [pk, increment]
  owner_acc_id int [ref: > accs.id]
  name text
  product_uniq_field_first text
}

// Таблица с товарами магазинов
Table shops_products {
  id int [pk, increment]
  owner_shop_id int [ref: > shops.id]
  name text
  product_uniq_field_second text
}

// Таблица с избранными аккаунтами для каждого аккаунта
Table favourite_accs_for_accs {
  acc_id int [ref: > accs.id]
  fav_acc_id int [ref: > accs.id]
}

// Таблица с избранными магазинами для каждого аккаунта
Table favourite_shops_for_accs {
  acc_id int [ref: > accs.id]
  fav_shop_id int [ref: > shops.id]
}



Brief description of the purpose of the tables and logic:
The system has accounts and shops (accs and shops) that can place goods (accs_products and shops_products), that is, accounts and shops are 2 types of shops.
Also, accounts can add these very different sites (shops and accounts) to favorites, so that later in the general feed you can see all the products of different types of stores (favourite_accs_for_accs and favorite_shops_for_accs).
Account and store products are similar and have common fields (name), but store and account products also have their own unique fields (product_uniq_field_first and product_uniq_field_second). And there can be either 5 or 10 such different fields.
In addition, there may be such a situation that in addition to the products of accounts and stores, there will be products of another separate entity, for example, groups (groups, groups_products, favorite_groups_for_accs) - the third type of stores.

Specific questions:
1. How it will be more correct to design tables in a DB? Create one single table for all products of all store types with a bunch of optional NULL fields, or a separate table for each store type, as it is now?
2. Key: based on the question above, what should a correct SQL query look like (PostgreSQL or an analogy in MySQL) according to best practices for getting goods of different types of stores (which the account has in favorites in two tables favorite_accs_for_accs and favorite_shops_for_accs) with pagination (with a strict number of elements per page) in one case or another?

I can't think through this moment, I really hope for your help, thank you in advance.

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question