T
T
TaxaCorbett2020-07-24 15:49:38
PostgreSQL
TaxaCorbett, 2020-07-24 15:49:38

Which option is best when designing a database of dissimilar stores?

Good day to all!
On the one hand, a trivial task arose, and on the other hand, not a very problem. I can not decide on a choice, since none fully suits me.
There is an entity "Shop", which stores basic information about the store (type, name, ...). The bottom line is that stores are different and products for each type of store are stored in separate tables. For example, for a store with the type "Car service", the goods are - services. And for a grocery store, the goods are toys.
That is, we have the "Shop" entity and other tables that store "goods" for this store.
Every store has a price tag for every product. Here is the question just in the tables for storing such price tags. It is necessary to organize access to price tags,
My options are:
Option 1: A
field is added to each entity from "products": price, shopType.
For each store, tables of the type are created: shopId, productId, price. By default, they are empty and JOIN on product is accessed. If price is empty, then we take the price from the table with the "Product". If the price changes, then these tables are filled.
This approach seems more logical, but creates quite a few tables for each store type.

Option 2:
An abstract Product table is created, in which we store a set of FK's: shopId, price, serviceProductId?, groceryProductId?. .... We create a CONSTRAINT for the fact that in one record there can be only one FK per table with a specific product.
Then it turns out that all our price tags are stored in one table, for different stores. For PK in this table, make some kind of serial.
This approach is not very popular due to the fact that you will have to build quite large queries with JOINs.

Maybe someone has better ideas? And then something I'm completely at a dead end) Thanks in advance

Answer the question

In order to leave comments, you need to log in

1 answer(s)
I
Ivan Shumov, 2020-07-24
@inoise

A separate database and separate projects are created for each store. And be sure to create a sticker on the monitor "do not complicate your life"

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question