关于数据库设计的问题 [英] A question about database design

问题描述

我有一种奇怪的关系，我希望得到一些关于处理它的最佳方式的意见。


3桌：

1. WAREHOUSES 。

2. PROD_CATEGORIES（产品中一对多）

3.产品（许多与PROD_CATEGORIES一对多）


通常，每个类别分配约100个产品。


有超过1000个仓库。在将产品分配到仓库时，通常会将产品类别分配给仓库，该类别中的所有产品都将存储在分配的仓库中。偶尔也有例外情况：产品类别将被分配到仓库，3或4个单独的产品将被排除，不会存储在仓库中。


设计数据库时，最好是在Warehouse-Products之间创建多对多关系，或者更好地在Warehouse-Prod_Categories之间创建它并在Warehouse-Product_Exceptions之间维护另一个多对多关系。


前者更容易存储并且更容易进行查询，但会使数据库膨胀。后者存储和查询会更复杂，但需要更少的存储空间。


只有一个人维护这个数据库，他在编写查询时很公平。


您认为设置这些表的最佳方式是什么？


谢谢，

Adam

I have an odd relationship and I would like to get some opinions on the best way to handle it.

3 tables:
1. WAREHOUSES.
2. PROD_CATEGORIES (One in One-to-many with PRODUCTS)
3. PRODUCTS (Many in One-to-many with PROD_CATEGORIES)

Usually, about 100 products are assigned to each category.

There are over 1000 warehouses. When allocating products to warehouses, typically you would assign the product category to the warehouse and all the products in that category would be stored in the assigned warehouse. Occasionally, there are exceptions: A product category will be assigned to a warehouse, and 3 or 4 individual products will be excluded and not stored in the warehouse.

When designing the database, is it better to create the Many-to-many relationship between Warehouse-Products, or is it better to make it between Warehouse-Prod_Categories and maintain another Many-to-many relationship between Warehouse-Product_Exceptions.

The former will be simpler to store and easier to make queries, but will bloat the database. The later will be more complicated to store and query, but it will require much less storage.

Only one guy maintains this database and he is fair at writing queries.

What do you think is the best way to set up these tables?

Thanks,
Adam

推荐答案

根据您的描述，WAREHOUSES& PROD_CATEGORIES不仅不够，也是不可能的。如果它已经设置好，那么你就没有例外。


如果你愿意，可以定义一些基本默认值，例如将PROD_CATEGORIES项链接到特定仓库，但是这样做会必须单独处理。然而，WAREHOUSES和产品之间的关系是必需的。


我希望这会有所帮助。

With what you describe, a simple One-to-Many between WAREHOUSES & PROD_CATEGORIES is not only not adequate, it is not possible. If it were set up, then you could have no exceptions.

You could define some basic defaults if you like, such as would link a PROD_CATEGORIES item with a particular warehouse, but this would have to be handled separately. A relationship between WAREHOUSES and PRODUCTS however, would be required.

I hope this helps.

Hi NeoPa，

感谢您的回复。是的，我知道仓库到产品类别中的多对一不会单独使用。我正在尝试在这两种选择之间作出决定：


第一选择：

仓库>多对多<产品分类

和

仓库>多对多<产品例外


或第二选择：

仓库>多对多<产品


第一选择不会使多对多关系表膨胀，但维护起来会更复杂。


第二种选择将更容易维护，但它将导致一个巨大的多对多表，我担心它会减慢整个应用程序。


我猜我的问题可归结为：什么使访问运行更顺畅：1）2个表和复杂查询中的数据量较少，或2）单个表中的数据量大得多，查询复杂度较低？


对不起，如果这是混乱的。感谢您的帮助。

Adam

Hi NeoPa,

Thanks for the reply. Yes, I''m aware the Many-to-one in warehouse to product category will not make it alone. I''m trying to decide between these 2 alternatives:

1st Choice:
Warehouses > Many-to-many < Product Categories
And
Warehouses> Many-to-many < Product EXCEPTIONS

or 2nd Choice:
Warehouse > Many-to-many < Products

The first choice will not bloat the many-to-many relationship tables, but it will be more complicated to maintain.

The second choice will be easier to maintain but it will lead to a huge many-to-many table and I''m concerned that it will slow the whole application down.

I guess my question boils down to this: What will make access run more smoothly: 1) smaller amounts of data in 2 tables and complicated queries, or 2) a much larger amount of data in a single table and less complicated queries?

Sorry if this is jumbled. Thanks for the help.
Adam

Adam，


从您的第一段开始，您似乎很清楚自己没有掌握我在说什么请仔细阅读我的上一篇文章。从我们已经达到的位置继续讨论似乎没什么意义。


如果有什么你无法理解的话请特别告诉我。我很高兴继续，但只是从头开始再次看到同样的问题毫无意义。

Adam,

From your first paragraph it seems clear you have not grasped what I was saying. Please read through my last post again carefully. There seems little point in continuing the discussion from a position behind where we have already reached.

If there''s anything you cannot understand then please let me know specifically. I''m happy to continue, but simply see no point in going over the same ground again from scratch.

这篇关于关于数据库设计的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！