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

Introduction fact tables store metric values and Foreign keys of dimension tables. Dimension table angle and category. Time, region, and status. The old method is select * fromorderoinnerjoindistrictdono. discoded. discodein.

Introduction fact tables store metric values and Foreign keys of dimension tables. Dimension table angle and category. Time, region, and status. The old method is select * fromorderoinnerjoindistrictdono. discode = d. discodein.

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 the Hong Kong Space. 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, a bool field exists, 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.

Insert some data.

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

DECLARE @ Has CHAR (1) -- 0 none, 1 Has, 3 ignore SET @ Has = '3' IF (@ Has = '3') BEGINSELECTp. productID, p. productNameFROM SWB_Demo.dbo.T_Products pENDELSEBEGINSELECTp. productID, p. productNameFROM SWB_Demo.dbo.T_Products pWHERE 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') BEGINIF (@ HasDiscount = '3') BEGINSELECTp. productID, p. productNameFROM SWB_Demo.dbo.T_Products pENDELSEBEGINSELECTp. productID, p. productNameFROM SWB_Demo.dbo.T_Products pWHERE p. hasDiscount = @ HasDiscountENDENDELSEBEGINIF (@ HasDiscount = '3') BEGINSELECTp. productID, p. productNameFROM SWB_Demo.dbo.T_Products pwhere p. has = @ HasENDELSEBEGINSELECTp. productID, p. productNameFROM SWB_Demo.dbo.T_Products pWHERE p. hasDiscount = @ HasDiscountAND p. has = @ HasENDEND

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. At least they seldom write stored procedures. American space is a nightmare for me.

It seems that you can use code to generate SQL and website space. You can add tables and fields as long as it is encapsulated. You do not need to modify the SQL every time. Since there is one, it is certainly possible, 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.

Insert the following data.

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 "IT architects in breakthrough" blog. Please keep this source