Database Design-from traditional methods to fact tables and dimension tables

Introduction

Fact table

The foreign key that stores the measurement value and dimension table.

Dimension Table

Angle and category. Time, region, and status.

Old Method

Select * from order oinner join district d on o. discode = d. discodeinner join address a on o. addressid =. addressidwhere o. createdate> '2014-2-5 'and o. createdate <'2014-12-5 'and o. isb2c = '1' and o. status = '1' and o. discode = '000000' and. address like 'Beijing %'

It is difficult to add conditions when the stored procedure is killed. When conditions change or new fields exist, many stored procedures often need to be modified. It is very painful to add an and condition, or even join an inner into a new table. Always think about whether there is a good way, but never come up with a good way.

I recently looked at the construction of the data warehouse and found the concepts of fact tables and dimension tables. I have a little bit of feeling in conjunction with my own projects.

In fact, some signs and States can all be seen as a dimension of sales information.

In general, it is not the condition field in the order table. In the past, a new requirement was to add fields directly to the order table. As the order table grew bigger and bigger, many fields were not directly related to the order, but I couldn't figure out what to do, I don't know which table should be attributed to. Is it a new table? Or what other tables? The final result is often added to the order table.

Multi-condition query, dynamic query, and comprehensive query in different dimensions.

It is actually a connection query in different dimensions. The more conditions, the more dimensions involved. Each time a dimension is added, a dimension table is connected, which can be made dynamic, and conditions can be written in the code, and database tables can be spliced, when fields and tables are added in the future, there is almost no need to change any code, including program code and SQL code, and no manual maintenance is required.

select*from order oinner join isb2c i on o.is=i.isinner join status s on o.statusid=s.statusidinner join address a on o.addressid=a.addressid

Database Design Methods

1. Traditional direct ing

In the previous data design, there was a bool field, which indicates yes or no ].

Here is an example of a product table.

Well, add a field to the table. HasDiscount indicates whether there is a discount.

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/22441911Y-0.png "title =" QQ1.png "style =" float: none; "/>

Insert some data.

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/22441a564-1.png "style =" float: none; "title =" QQ2.png "/>

One scenario is to query the product. one of the conditions is whether there is a discount. There are three conditions:

1. Discounts are available.

2. No discount.

3. Ignore this condition, that is, it is queried no matter whether it is available or not.

DECLARE @ Has CHAR (1) -- 0 none, 1 Has, 3 ignore SET @ Has = '3' IF (@ Has = '3') begin select p. productID, p. productName FROM SWB_Demo.dbo.T_Products p endelsebegin select p. productID, p. productName FROM SWB_Demo.dbo.T_Products p WHERE p. hasDiscount = @ HasEND

In the preceding SQL, @ Has can also be written to determine whether the value is null. The same effect can be achieved as long as it is different from other values of 0 and 1.

It seems that it still works, but the demand has changed. You need to add a bool field. There are also three situations in the query scenario.

Okay, it's another branch. continue adding.

DECLARE @ HasDiscount CHAR (1) -- 0 none, 1 Has, 3 ignore SET @ HasDiscount = '3' DECLARE @ Has CHAR (1) -- 0 none, 1 Has, 3 ignore SET @ Has = '1' IF (@ Has = '3') begin if (@ HasDiscount = '3') begin select p. productID, p. productName FROM SWB_Demo.dbo.T_Products p end else begin select p. productID, p. productName FROM SWB_Demo.dbo.T_Products p WHERE p. hasDiscount = @ HasDiscount endendelsebegin if (@ HasDiscount = '3') begin select p. productID, p. productName FROM SWB_Demo.dbo.T_Products p where p. has = @ Has end else begin select p. productID, p. productName FROM SWB_Demo.dbo.T_Products p WHERE p. hasDiscount = @ HasDiscount AND p. has = @ Has ENDEND

Okay, I got the requirement done, and said, "Don't add this attribute any more. Otherwise, no one can maintain this SQL statement. I want to escape !". But the demand will definitely change, and in many cases such attributes are indispensable. What should we do? Do we all do this?

It seems that people do not need to write stored procedures, or at least seldom write them. Why is it my nightmare.

I heard that it seems that I can use code to generate SQL statements. As long as the encapsulation is good, I can add tables and fields without modifying the SQL statements each time. Since there is one, it is certainly feasible, at least to some extent, it can reduce the development workload, because this property cannot be exhaustive.

2. fact table dimension table

After a few days of learning fact tables and dimension tables, I have a little idea.

First, modify the structure of the product table as follows.

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/2244193N4-2.png "title =" QQ7.png "style =" float: none; "/>

The product table only contains product-related information. discounts can be viewed as a dimension.

Create a discount table, for example.

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/22441a2Q-3.png "style =" float: none; "title =" QQ3.png "/>

Insert the following two pieces of data, one representing a discount, and the other representing a non-discount.

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/22441a340-4.png "style =" float: none; "title =" QQ4.png "/>

Create a discount relationship table to store the relationship data between products and discounts.

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/2244192O0-5.png "style =" float: none; "title =" QQ5.png "/>

Insert the following data.

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/2244192256-6.png "style =" float: none; "title =" QQ6.png "/>

At this time, the SQL statement can be written as follows.

-- Whether or not to discount this condition SELECT * FROM SWB_Demo.dbo.T_Products pWHERE p. productName LIKE '% fruit %' -- whether to discount this condition SELECT * FROM SWB_Demo.dbo.T_Products pINNER JOIN SWB_Demo.dbo.T_ProductHasDiscount php ON p. productID = php. productIDINNER JOIN SWB_Demo.dbo.T_HasDiscount ph ON php. hasID = ph. hasIDAND ph. hasNot = '1' WHERE p. productName LIKE '% fruit %'

The link between the discount table and the discount relationship table can be dynamically spliced. The entire SQL statement is generated using code and the SQL statement does not need to be modified every time. Is it better?

This article is from the "breakthrough IT architects" blog, please be sure to keep this source http://virusswb.blog.51cto.com/115214/1205589