blocks|key|3774686|text|在Basket表中，product_id似乎是多余的。此外，我可能会创建一个跟踪不同价格的表(当然，如果价格随着时间的推移而变化)。所以我会有：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3774687|Product_Suppliers上的|3774688|Product_Suppliers+(id+(PK),+product_id+(FK+to+Products),+supplier_id+(FK+to+suppliers),++active(flag),+UNIQUE+constraint+on+(product_id+%2B+supplier_id))+|ordered-list-item|3774689|Product_Suppliers_Versions+(id+PK,+product_suppliers_id(FK+to+Product_Suppliers),+price,+effective_date)|3774690|Basket+(id+PK,+prodcut_supplier_version_id(FK+to+Product_Suppliers_Versions),+quantity)|3774691|AFTER+UPDATE,+INSERT+TRIGGER将以前的版本复制到Product_Suppliers_Versions)，如果至少有一个购买绑定到supplier_id+and+product_id，则阻止对它进行修改。|3774692|3774693|entityMap^0|1|6|A|A|0|0|0|47|0|0|2W|0|0|2F|0|0|S|25|Q|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@$9|U|A|V|B|C]|$9|W|A|X|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Y|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|Z|8|@$9|10|A|11|B|C]]|D|@]|E|$]]|$1|K|3|L|5|J|7|12|8|@$9|13|A|14|B|C]]|D|@]|E|$]]|$1|M|3|N|5|J|7|15|8|@$9|16|A|17|B|C]]|D|@]|E|$]]|$1|O|3|P|5|J|7|18|8|@$9|19|A|1A|B|C]|$9|1B|A|1C|B|C]]|D|@]|E|$]]|$1|Q|3|-4|5|6|7|1D|8|@]|D|@]|E|$]]|$1|R|3|-4|5|6|7|1E|8|@]|D|@]|E|$]]]|S|$]]

<code>product_id</code> seems to be redundant in <code>Basket</code> table. Also, I'd probably create a table that keeps track of different prices (surely, if prices change over the time). So I'd have:

<ol>
<li><code>Product_Suppliers (id (PK), product_id (FK to Products), supplier_id (FK to suppliers), 
active(flag),
UNIQUE constraint on (product_id + supplier_id))</code> </li>
<li><code>Product_Suppliers_Versions (id PK, product_suppliers_id(FK to Product_Suppliers), price,
effective_date)</code></li>
<li><code>Basket (id PK, prodcut_supplier_version_id(FK to Product_Suppliers_Versions), quantity)</code></li>
<li><code>AFTER UPDATE, INSERT TRIGGER</code> on Product_Suppliers that copies previous version to Product_Suppliers_Versions) and prevents modification of <code>supplier_id and product_id</code> if there was at least one purchase tied to it.</li>
</ol>

blocks|key|2160908|text|从技术上讲，Basket可以省略product_id，因为这可以通过在product_suppliers_id上对Product_Suppliers进行联接来获得。这可能会使您的SQL变得有点复杂，因此可能值得将值保持在更接近您需要的位置。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2160909|entityMap^0|6|6|G|A|Z|K|1L|H|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]|$9|K|A|L|B|C]|$9|M|A|N|B|C]|$9|O|A|P|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|G|$]]

Technically, <code>Basket</code> can omit <code>product_id</code> since this is available through a join against <code>Product_Suppliers</code> on <code>product_suppliers_id</code>. This might complicate your SQL a bit, so it may be worth keeping the value closer to where you need it.

blocks|key|3774769|text|在Basket中，您应该具有：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3774770|basket_id+(PK)
product_id
supplier_id
quantity
price|code-block|syntax|javascript|3774771|Product_Suppliers只是一个配置表。它具有供应商提供的产品的唯一当前配置。你的Basket应该比指向Product_Suppliers“更了解”，因为：|3774772|3774773|产品可以更改价格。供应商也可以在给定的时间点停止提供|unordered-list-item|3774774|产品。|3774775|3774776|你的设计应该允许你正确地查询历史数据，即使产品改变了价格，或者供应商不再提供它。|3774777|因此，您不应该在其中包含product_suppliers_id。|3774778|而且，我在你的设计中看不到谁的篮子，这意味着你还应该有一个供客户或用户使用的表(或者两者，如果用户可以输入他们自己的订单，商店内部用户可以为客户输入订单)和相应的键(client_id，user_id)。|3774779|entityMap^0|1|6|0|0|0|H|1A|6|1L|H|0|0|0|0|0|0|C|K|0|0^^$0|@$1|2|3|4|5|6|7|11|8|@$9|12|A|13|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|14|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|15|8|@$9|16|A|17|B|C]|$9|18|A|19|B|C]|$9|1A|A|1B|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|1C|8|@]|D|@]|E|$]]|$1|N|3|O|5|P|7|1D|8|@]|D|@]|E|$]]|$1|Q|3|R|5|P|7|1E|8|@]|D|@]|E|$]]|$1|S|3|-4|5|6|7|1F|8|@]|D|@]|E|$]]|$1|T|3|U|5|6|7|1G|8|@]|D|@]|E|$]]|$1|V|3|W|5|6|7|1H|8|@$9|1I|A|1J|B|C]]|D|@]|E|$]]|$1|X|3|Y|5|6|7|1K|8|@]|D|@]|E|$]]|$1|Z|3|-4|5|6|7|1L|8|@]|D|@]|E|$]]]|10|$]]

In <code>Basket</code>, you should have:

<pre><code>basket_id (PK)
product_id
supplier_id
quantity
price
</code></pre>

<code>Product_Suppliers</code> is merely a configuration table. It has the only the current configuration of a product offered by a Supplier. Your <code>Basket</code> should 'know better' then pointing to <code>Product_Suppliers</code> because:

<ul>
<li>Products can change price. </li>
<li>Products can also cease to be offered by a Supplier at a given point. </li>
</ul>

Your design should allow you to query for historical data correctly, even if a product changes the price or if it's not offered by the supplier anymore.

Thus, you should not have <code>product_suppliers_id</code> in it. 

Also, I don't see in your design whose basket that is, meaning you should also have a table for Clients or Users (or both, if users can input their own orders AND the store internal users can input orders for the clients) and the corresponding key(s) (client_id, user_id).

blocks|key|312861|text|你可能想要分配给篮子自己的PK+(以防你想要一个包含多个产品的篮子，或者相同的产品和不同的提供商)。如果您想在数据库级别强制产品和购物篮之间建立一对一关系，则可以为(basket_id，product_id)分配唯一的索引|type|unstyled|depth|inlineStyleRanges|entityRanges|data|312862|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

You may want to assign to Basket its own PK (in case you ever want a basket with several products, or the same product and different providers). You could assign a unique index to (basket_id,product_id) if you want to force one-to-one relation between products and baskets at the db level

blocks|key|312908|text|这还远不是最终的结果，而只是指向某个方向。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|312909|一个Basket可能有很多项，所以引入了BasketItem。|offset|length|style|CODE|312910|BasketItemNo是序数(1,2,3+.)对于每个BasketID。|312911|将ItemPrice复制到BasketItem.UnitPrice，以便将来进行价格更改。任何价格变化都不应改变历史篮子数据。|312912|如果ProductDescription也可能发生变化，那么也应该在购买时进行复制。|312913|​|312914|📷|atomic|312915|312916|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/73c08f34744b3924d7b514ad7d6b0a1e.png|imageAlt^0|0|2|6|K|A|0|0|C|S|8|0|1|9|D|K|0|2|I|0|0|0|1|0|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|13|8|@$D|14|E|15|F|G]|$D|16|E|17|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|18|8|@$D|19|E|1A|F|G]|$D|1B|E|1C|F|G]]|9|@]|A|$]]|$1|J|3|K|5|6|7|1D|8|@$D|1E|E|1F|F|G]|$D|1G|E|1H|F|G]]|9|@]|A|$]]|$1|L|3|M|5|6|7|1I|8|@$D|1J|E|1K|F|G]]|9|@]|A|$]]|$1|N|3|O|5|6|7|1L|8|@]|9|@]|A|$]]|$1|P|3|Q|5|R|7|1M|8|@]|9|@$D|1N|E|1O|1|1P]]|A|$]]|$1|S|3|O|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|T|3|-4|5|6|7|1R|8|@]|9|@]|A|$]]]|U|$V|$5|W|X|Y|A|$Z|10|11|-4]]]]

This is far from final, but just to point in a certain direction.

A <code>Basket</code> may have many items, so <code>BasketItem</code> is introduced.

<code>BasketItemNo</code> is ordinal number (1,2,3 ..) for each <code>BasketID</code>.

<code>ItemPrice</code> is copied to <code>BasketItem.UnitPrice</code> in order to allow for future price changes. Any price change should not change historic basket data.

If <code>ProductDescription</code> may change too, then that also should be copied at a time of purchase.

<img src="https://i.stack.imgur.com/uPJgC.png" alt="enter image description here">

I am developing a stock ordering system in PHP/MySQL. Products can have multiple suppliers. Products are added to the basket by specifying the quantity required and the supplier. This is the current DB Structure:

<pre><code>Products
=========
id (PK)
name
etc...

Suppliers
==========
id (PK)
name
etc...

Product_Suppliers
=================
id (PK)
product_id (PK)
supplier_id (PK)
price

Basket
=======
product_id (PK)
quantity
product_suppliers_id
</code></pre>

The Product_Suppliers table stores the different suppliers and their prices for each product.

When adding a product to the basket, it first checks to see if the product_id already exists in the Basket table - if it does then it will simply overwrite the record. If it does not exist then it will create a new record.

The product_suppliers_id field in the Basket table specifies which supplier has been selected for that particular product.

I wanted to know whether I have got the correct approach here, or whether I need to make any changes to eliminate any redundancy.

MySQL Database Structure

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我正在用PHP/MySQL开发一个股票订购系统。产品可以有多个供应商。通过指定所需数量和供应商，将产品添加到篮子中。这是当前的DB结构：Products=========id (PK)nameetc...Suppliers==========id (PK)nameetc...Product_Suppliers=====...

问MySQL数据库结构
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL数据库结构EN