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

Source: Internet
Author: User


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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.