CODEWITHSUNDEEP
sqldatabasesqlite
Martin
Martin

Reputation: 9369

Database schema design issue

I have two tables in a SQLite DBMS:

Shop(PK, A1, A2, A3) where PK is Primary Key `A1..An` are nullable attributes;
Product(PK, FK) where FK references Shop(PK) and PK|FK is Primary Key

Shop typically has 5 or 6 entries in a database instance.

The problem is that when a new product is inserted, it is very often present in ~all the shops, so now the user effectively has to insert 5 or 6 rows at a time (where PK is repeated in each row - PK consists of a long attributes in the real case).

I wonder if there's a way to make the life easier for the user by associating one new product to all the shops by default, by either refactoring the schema (e.g. maybe using flags?) or by triggering all the insertions when a new product appears (is it tricky?), or both the things. Note that one product must be present in at least one shop. I want the solution to be as less obscure as possible and easy to maintain.

Upvotes: 1

Views: 62

Answers (1)

Adil El
Adil El

Reputation: 44

It's an N->N relation between your two tables, in my opinion the FK shouldn't be in the product table, you should have another table "PRODUCT_SHOP" where you'd have PKSHOP, PKPRODUCT. To answer your question there is a lot of tricky ways to do this, none of them is good since you'de have to use code to interpret your tricks, the best way is to respect the standrads and insert as many times your poduct with the according shop in the PRODUCT_SHOP table(unless your have a lot, like really a lot of shops, the table PRODUCT_SHOP is basically a PRODUCTS x SHOPS). It really depends on the size of your database, if it's not huge stick with relational basic data. Hope i helped

Upvotes: 3

Related Questions