database 库存数据库的最佳结构

Best structure for inventory database

提问by Saif Bechan

I want to create a small database for my inventory but I have some problems on picking a structure. The inventory will be updated daily at the end of the day.

我想为我的库存创建一个小型数据库，但我在选择结构时遇到了一些问题。库存将在每天结束时更新。

The problem I am facing is the following.

我面临的问题如下。

I have a table for my products, having an

我有一张桌子放我的产品，有一个

id, name, price, quantity.

Now I have another table for my sales, but there is my problem. What kind of fields do I need to have. At the end of the day I want to store a record like this:

现在我的销售有另一张桌子，但这是我的问题。我需要有什么样的字段。在一天结束时，我想存储这样的记录：

20       product_x       $ 5,00         $ 100,-
20       product_y       $ 5,00         $ 100,-
20       product_z       $ 5,00         $ 100,-
20       product_a       $ 5,00         $ 100,-
-------------------------------------------------
                                        $ 400,-

So how do I model this in a sales record. Do I just create a concatenated record with the product id's comma separated.

那么我如何在销售记录中对此进行建模。我是否只是创建一个以产品 ID 的逗号分隔的连接记录。

Or is there another way do model this the right way.

或者是否有另一种方法以正确的方式对此进行建模。

采纳答案by Tom Anderson

I'd have a table with a row per item per day - store the date, the item ID, the quantity sold, and the price sold at (store this even though it's also in the product table - if that changes, you want the value you actually sold at preserved). You can compute totals per item-day and totals per day in queries.

我有一个表格，每天每件商品都有一行 - 存储日期、商品 ID、销售数量和销售价格（即使它也在产品表中，也将其存储 - 如果发生变化，您需要您实际以保藏价出售的价值）。您可以在查询中计算每个项目日的总数和每天的总数。

Tables:

表格：

create table product (
  id integer primary key,
  name varchar(100) not null,
  price decimal(6,2) not null,
  inventory integer not null
);

create table sale (
  saledate date not null,
  product_id integer not null references product,
  quantity integer not null,
  price decimal(6,2) not null,
  primary key (saledate, product_id)
);

Reporting on a day:

一天报告：

select s.product_id, p.name, s.quantity, s.price, (s.quantity * s.price) as total
from product p, sale s
where p.id = s.product_id
and s.saledate = date '2010-12-5';

Reporting on all days:

全天报告：

select saledate, sum(quantity * price) as total
from sale
group by saledate
order by saledate;

A nice master report over all days, with a summary line:

一个很好的主报告，有一个总结行：

select *
from (
    (select s.saledate, s.product_id, p.name, s.quantity, s.price, (s.quantity * s.price) as total
    from product p, sale s
    where p.id = s.product_id)
  union
    (select saledate, NULL, 'TOTAL', sum(quantity), NULL, sum(quantity * price) as total
    from sale group by saledate)
) as summedsales
order by saledate, product_id;

回答by Wajira Weerasinghe

This is a model which supports many aspects,

这是一个支持多方面的模型，

1. Supports Sites, Locations and Warehouses etc.
2. Supports Categorization and Grouping
3. Support Generic Product (Ex. "Table Clock" and specific product "Citizen C123 Multi Alarm Clock" )
4. Also support Brand Variants (by various manufacturers)
5. Has CSM (color / size / model support) Ex. Bata Sandles (Color 45 Inch Blue color)
6. Product Instances with serials (such as TVs , Refrigerators etc.)
7. Lot control / Batch control with serial numbers.
8. Pack Size / UOM and UOM Conversion
9. Manufacturer and Brands as well as Suppliers
10. Also included example transaction table (Purchase order)
11. There are many other transaction types such as Issues, Transfers, Adjustments etc.

1. 支持站点、位置和仓库等。
2. 支持分类和分组
3. 支持通用产品（例如“台式时钟”和特定产品“Citizen C123 Multi Alarm Clock”）
4. 还支持品牌变体（由不同的制造商提供）
5. 有 CSM（颜色/尺寸/型号支持）Bata 凉鞋（颜色 45 英寸蓝色）
6. 带有序列号的产品实例（如电视、冰箱等）
7. 带序列号的批次控制/批次控制。
8. 包装尺寸/计量单位和计量单位转换
9. 制造商和品牌以及供应商
10. 还包括示例交易表（采购订单）
11. 还有许多其他交易类型，例如问题、转移、调整等。

Hope this would help. Please let me know if you need further information on each table.

希望这会有所帮助。如果您需要有关每张桌子的更多信息，请告诉我。

Cheers...!!!

干杯...！！！

Wajira Weerasinghe.

瓦吉拉·韦拉辛哈。

Sites

网站

• id
• site_code
• Site_name

• ID
• 站点代码
• 站点名称

Warehouse

仓库

• id
• site_id
• warehouse_code
• warehouse_name

• ID
• site_id
• 仓库代码
• 仓库名称

Item Category

项目类别

• id
• category_code
• category_name

• ID
• 类别代码
• 分类名称

Item Group

项目组

• id
• group_code
• group_name

• ID
• 组码
• 团队名字

Generic Product

通用产品

• id
• generic_name

• ID
• 通用名

Product

产品

• id
• product_code
• category_id
• group_id
• brand_id
• generic_id
• model_id/part_id
• product_name
• product_description
• product_price (current rate)
• has_instances(y/n)
• has_lots (y/n)
• has_attributes
• default_uom
• pack_size
• average_cost
• single_unit_product_code (for packs)
• dimension_group (pointing to dimensions)
• lot_information
• warranty_terms (general not specific)
• is_active
• deleted

• ID
• 产品代码
• 类别编号
• group_id
• 品牌标识
• generic_id
• model_id/part_id
• 产品名称
• 产品描述
• product_price（当前价格）
• has_instances(y/n)
• has_lots (y/n)
• has_attributes
• default_uom
• 包装尺寸
• 平均成本
• single_unit_product_code（用于包装）
• 维度组（指向维度）
• 批次信息
• 保修条款（一般不具体）
• 活跃
• 已删除

product attribute type (color/size etc.)

产品属性类型（颜色/尺寸等）

• id
• attribute_name

• ID
• 属性名称

product_attribute

产品属性

• id
• product_id
• attribute_id

• ID
• 产品编号
• 属性 ID

product attribute value (this product -> red)

产品属性值（此产品 -> 红色）

• id
• product_attribute_id
• value

• ID
• product_attribute_id
• 价值

product_instance

产品实例

• id
• product_id
• instance_name (as given by manufacturer)
• serial_number
• brand_id (is this brand)
• stock_id (stock record pointing qih, location etc.)
• lot_information (lot_id)
• warranty_terms
• product attribute value id (if applicable)

• ID
• 产品编号
• instance_name（由制造商提供）
• 序列号
• brand_id（是这个品牌）
• stock_id（指向qih、位置等的库存记录）
• 批次信息（批次 ID）
• 保修条款
• 产品属性值 ID（如果适用）

product lot

产品批次

• id
• lot_code/batch_code
• date_manufactured
• date_expiry
• product attribute value id (if applicable)

• ID
• lot_code/batch_code
• 生产日期
• 日期_到期
• 产品属性值 ID（如果适用）

Brand

牌

• id
• manufacturer_id
• brand_code
• brand_name

• ID
• 制造商_id
• 品牌代码
• 品牌

Brand Manufacturer

品牌制造商

• id
• manufacturer_name

• ID
• 生产商名称

Stock

库存

• id
• product_id
• warehouse_id, zone_id, level_id, rack_id etc.
• quantity in hand
• product attribute value id (if applicable) [we have 4 red color items etc.]

• ID
• 产品编号
• 仓库 ID、区域 ID、级别 ID、机架 ID 等。
• 手头数量
• 产品属性值 id（如果适用）[我们有 4 个红色项目等]

Product Price Records

产品价格记录

• product_id
• from_date
• product_price

• 产品编号
• 从日期
• 产品价格

Purchase Order Header

采购订单标题

• id
• supplier_id
• purchase_date
• total_amount

• ID
• 供应商_id
• 购买日期
• 总金额

Purchase Order Line

采购订单行

• id
• po_id
• product_id
• unit_price
• quantity

• ID
• po_id
• 产品编号
• 单价
• 数量

Supplier

供应商

• id
• supplier_code
• supplier_name
• supplier_type

• ID
• 供应商代码
• 供应商名称
• 供应商类型

product_uom

product_uom

• id
• uom_name

• ID
• uom_name

product_uom_conversion

product_uom_conversion

• id
• from_uom_id
• to_uom_id
• conversion_rule

• ID
• from_uom_id
• to_uom_id
• 转换规则

回答by Will A

Try modelling your sales as a transaction - with a "header", i.e. who sold to, when sold, invoice # (if applicable), etc. and "line items", i.e. 20 * product_x @ $5 = $100. The safest approach is to avoid relying upon prices etc. from the products table - as these will presumably change over time, and instead copy much of the product information (if not all) into your line item - so even when prices, item descriptions etc. change, the transaction information remains as was at the time the transaction was made.

尝试将您的销售建模为交易 - 使用“标题”，即销售对象、销售时间、发票编号（如果适用）等和“行项目”，即 20 * product_x @ $5 = $100。最安全的方法是避免依赖产品表中的价格等 - 因为这些可能会随着时间的推移而改变，而是将大部分产品信息（如果不是全部）复制到您的订单项中 - 所以即使价格、项目描述等. 更改后，交易信息将保持交易发生时的状态。

回答by HLGEM

Inventory can get quite complex to model. First you need to understand that you need to be able to tell the value of the inventory onhand based on what you paid for it. This means you cannot rely on a product table that is updated to the current price. While you might want such a table to help you figure out what to sell it for, there are tax reasons why you need to know the actual vlaue you paid for each item in the warehouse.

库存可能会变得非常复杂。首先，您需要了解您需要能够根据您支付的费用来判断现有库存的价值。这意味着您不能依赖更新为当前价格的产品表。虽然您可能需要这样的表格来帮助您确定出售它的目的，但出于税收原因，您需要知道您为仓库中的每件商品支付的实际价值。

So first you need the product table (you might want to make sure you have an updated date column in this, it can be handy to know if your prices seem out of date).

所以首先你需要产品表（你可能想确保你有一个更新的日期列，知道你的价格是否过时会很方便）。

Then you need a table that stores the actual warehouse location of each part and the price at purchase. If the items are large enough, you need a way to individually mark each item, so that you know what was taken out. Usually people use barcodes for that. This table needs to be updated to record that the part is no longer there when you sell it. I prefer to make the record inactive and have a link to my sales data to that record, so I know exactly what I paid for and what I sold each part for.

然后您需要一个表来存储每个零件的实际仓库位置和购买价格。如果物品足够大，您需要一种方法来单独标记每个物品，以便您知道取出了什么。通常人们为此使用条形码。需要更新此表以记录零件在您出售时不再存在。我更喜欢将记录设为非活动状态，并将我的销售数据链接到该记录，因此我确切地知道我支付了什么费用以及我出售了每个零件的价格。

Sales should have at least two tables. One for the general information about the sale, the customername (there should also be a customer table most of the time to get this data from), the date, where it was shipped to etc.

销售应该至少有两张桌子。一个是关于销售的一般信息、客户名称（大多数时候也应该有一个客户表来获取这些数据）、日期、发货地点等。

Then a sales detail table that includes a record for each line item in the order. Include all the data you need about the part, color, size, quantity, price. This is not denormalizing, this is storing historical data. The one thing you do not want to do is rely on the prices in the product table for anything except the inital entry to this table. You do not want to do a sales report and have the numbers come out wrong becasue the product prices changed the day before.

然后是销售明细表，其中包括订单中每个行项目的记录。包括您需要的有关零件、颜色、尺寸、数量、价格的所有数据。这不是非规范化，这是存储历史数据。您不想做的一件事是依赖产品表中的价格来获取除此表的初始条目之外的任何内容。您不想做销售报告并且因为前一天产品价格发生变化而导致数字出现错误。

Do not design an inventory database without consulting with an accountant or specialist in taxes. You also should do some reading on internal controls. It is easy to steal from a company undetected that has not done their work on internal controls in the database.

不要在没有咨询会计师或税务专家的情况下设计库存数据库。您还应该阅读有关内部控制的一些资料。很容易从未被发现的公司窃取数据，该公司尚未完成数据库中的内部控制工作。

回答by csi

Try multiple tables with links

尝试多个带有链接的表

table_products
id
name

table_product_sales
id
product_id
quantity
price_per
transaction_time AS DATETIME

SELECT table_product_sales.*, table_product.name 
FROM table_product
JOIN table_product_sales
ON table_product_sales.product_id = table_product.id
GROUP BY DATE(transaction_time)

Haven't tried it but will something like that work? That allows you to keep each transactions separate so you can query things like average number sold per sale, total sold per date, total sales each day, etc.

还没有尝试过，但这样的事情会奏效吗？这允许您将每笔交易分开，以便您可以查询诸如每次销售的平均销售数量、每个日期的销售总额、每天的销售总额等。

回答by lvil

I think you need a table with fields showing the transaction properties per customer OR a table with fields - date, product(foreign), quantity - this way you'll have no problem with new products

我认为您需要一个包含显示每个客户的交易属性的字段的表格或一个包含字段的表格 - 日期、产品（国外）、数量 - 这样您就不会遇到新产品的问题