数据库中的更改
1-已从IC_ProductFeature表中删除列维度
2-添加了新的表IC_ProductDimensions以保存产品的不同维度数据。以下是IC_ProductDimensions中使用的不同列的详细说明
1- DimensionId - This is the primary key in the table and of bigint datatype.
2- UOMID - (FK) Unit of Measurement ID , this is refrenced from the table iC_ProductUnitOfMeasure.
3- ProductFeatureId : This is referenced column from the iC_ProductFeature table.
4- NumericValue : This column stores the dimensional value (e.g. if UOM is pound and this column stores 10 than we can say that Weight of product is 10 pound.
3-在IC“ProductUnitOfMeasure”中添加了一列“MeasurementType”。
4-将productFeatureID的数据类型从varchar(100)更改为bigint。
数据流
IC_ProductUnitOfMeasure中的“MeasurementType”将存储为其创建Mesaure单位的度量。
e.g. let's take a snapshot view of rows in iC_ProductUnitOfMeasure
UOMID Abbrivation MeasurementType Description
1 lb Weight This UOM is used for measuring weight of the product
2 inch. Width This UOM is used for measuring width of the product
3 meter Length This UOM is used for measuring length of the product
4 Kg Weight This UOM is used for measuring weight of product in Kg.
在上面的示例中,我们用关联度量类型标记了每个UOM。
UOM 1可用于以磅为单位测量产品重量,同样UOM 4可用于以千克为单位测量产品重量。
加入产品长度为0.5米,产品宽度为50英寸,产品重量为100磅的产品。
1-在IC_product表中创建一个记录,以存储产品的公共属性。(供应产品ID=ICPR0001)
2-在IC_ProductFeature中创建一个记录,并存储常见的产品功能,如颜色、尺寸、S/W、H/W功能(如果有)。(假设productFeatureID=1)
3-在IC_ProductDimension表中创建3条记录,如下所示。
DimensionID UOMID ProductFeatureId NumericValue
1 1 1 100
2 2 1 50
3 3 1 0.5
4-在IC_ProductFeatureApplications中添加一条记录(productID=icpr0001,productFeatureID=1)
查询数据以获得产品ICPR001的不同尺寸值
select
product .* ,
productFeature.* ,
( CAST (dimension.NumericValue as Varchar(100) )+" " + UOM.Abbrivation) as Dimension ,
dimension.MeasurementType
from
iC_Product product ,
iC_ProductFeature productFeature ,
iC_ProductFeatureApplicability ,
iC_ProductDimesions dimension ,
iC_ProductUnitOfMeasure UOM
where
iC_ProductFeatureApplicability.ProductId = product.ProductId
and
iC_ProductFeatureApplicability.ProductFeatureId = productFeature.ProductFeatureId
and
dimension.ProductFeatureId = productFeature.ProductFeatureId
and
dimension.UOMID= UOM.UOMID
and
product.ProductId = 'ICPR001'
