Creating a relationship between three tables

Question

There are many ways I can think of to crack this together, but I want to know what is best here:

I have three tables. Products, price lists and prices.

One product may belong to many price lists.

One price list may belong to many products.

This relationship is many, many, and as far as I know, a Junction table (pricelist_products) is required. It works well.

Now one product in the price list can have several prices. A product only ever gets a price when it is included in the price list.

What I thought of is using the identifier from the pricelist_products navigation table as a foreign key in the price table, but is it really ... hacky?

Fishy example:

Product 1 - fishing rod.

Price List A - Fishermen.

Price List B - Fishingshop.

Price List A, Product 1, Price 1: (Monthly repayment option 1 (no deposit))

Price list A, Product 1, price 2: (Monthly repayment option 2 (with deposit))

Price List A, Product 1, Price 3: (Quaterly repayment)

Price List B, Product 1, Price 1: (Quaterly repayment)

Answer 1

What I thought of is using the identifier from the pricelist_products navigation table as a foreign key in the price table, but is it really ... hacky?

Perhaps the problem here is just one of the perspectives. The purpose of the join table is to uniquely identify each combination within your many-to-many relationship (initially: from pricelist to product ). This can be achieved in the connection table with the product_id and pricelist_id fields alone and without the surrogate id key.

Of course, if you defined your connection table with PRIMARY KEY (product_id, pricelist_id) , this table would not have the ability to uniquely identify combinations when considering price . Thus, you add the third id to the connection table. It looks like you considered this field as a necessary surrogate key when defining the relationship between two tables. However, since the real usefulness of this field refers to the third table, you can name it price_id instead, name your join table pricelist_product_price and define the primary key in all three fields (for example). This more clearly demonstrates the purpose of each area, and therefore, in practice, “hacks” may not be felt.

I do not know if this is the best practice for database design, but keep in mind that there is no reason why you should completely normalize each database. You want good performance with sufficient flexibility and scalability (this can mean one thing for a casual blog, and also quite another for a small business), and this can often be achieved with some degree of abnormal design.

Edited to add: Well, there is another change that I forgot to mention that falls under the “good” design or best practices. In your photo, you have two ID fields in the price table, where this will be enough. As @Gilbert Le Blanc pointed out, you should try to avoid ambiguous field names, for example, have multiple id fields, even if they are in different tables. This will help you see the usefulness of your fields, identify natural keys and eliminate redundancy.

Answer 2

If you will not use the connection between products and price lists anywhere, but prices, then an alternative design is as follows:

-Table products with fields: id, others

- Body price lists with fields: id, others

-Table prices with fields: id (auto-increment), product_id, pricelist_id, price

and you define an index (not unique) in a pair of fields product_id, pricelist_id