The "third paradigm" that the database design "sometimes has to violate"

The "third paradigm" that the database design "sometimes has to violate"

In the blog park, I saw an article on the database paradigm, "Five paradigms in database design":
The third normal rule lookup is to eliminate attributes that do not directly depend on the primary key of the table formed by the first and second paradigms. We have created a new table for all the information that is not associated with the primary key of the table. Each new table holds information from the source table and the primary key that they depend on.
About the third paradigm of the idea, I think a lot of friends are familiar with, in the database design, is also one of the paradigms we use as much as possible, what is the starting point of the third paradigm? is to minimize the "data redundancy", and also can get "data" cleanliness, improve maintenance, no doubt, the third paradigm is what we strive to comply with.
However, there are many friends to the third paradigm as the "immortal magic Weapon", but in fact, in the actual application, we still need to start from different business, the rational application of the "third paradigm." Let me give you a simple example:
An order will be associated with a lot of basic information, such as: customer, payment terms, freight, etc., this information is a special table for maintenance, in the next order is also used in the dropdown box selection, in the preservation of the order information, in accordance with the "third paradigm" requirements, it should only save the corresponding primary key value is OK. Because this avoids data redundancy, but for me, I would not do this, I will be the name of the customer, contact telephone, payment terms, such as the name of the order to record the information required to copy directly into the order table.
In this way, we violate the "third paradigm", yes, but here we have a reason to violate the "third paradigm":
1 I do not want to delete a payment term after the order has been issued, so that these orders cannot know the "real payment terms", which is certainly unreasonable.
2 I do not want to, because under this order, and "strict control" payment terms of the "delete" function, which is unreasonable, why can not be deleted? The "terms" will never be adopted next month.
3 I also do not want to, after the payment terms have been modified, resulting in all previous orders to adopt this payment terms into a new term, that the order in the system and the paper orders at hand to correspond again, this is certainly not reasonable.

Therefore, my design principle is that for such orders we should be "isolated" way to treat, so that the basic data copy into the order, which will certainly violate the so-called "third paradigm", but this is also the actual need AH. There is a difference between theory and practice.
Orders--This in reality in the form of physical existence, the physical with the basic data reference, rather than relevance, the basic data can only be as an order of this kind of "reference", rather than "association", which can be called "independence"; Moreover, the order has a certain historical, because it is physical, in the actual process, is generated immediately, then at the time of the generation to reference the basic data, the order was determined at that time, do not because the basic data changes caused by the order is "innocent degeneration", which is the "historical" order, when the next page of these paper orders can also correspond to the order in the system.

This is the most typical example I understand, in the actual system design, we should think more about, is not to adopt the "third paradigm", do not blindly sought after.

Above is purely my personal opinion, for reference only, welcome everybody to discuss.

Feedback

#1楼 Reply Reference View

2005-05-08 14:23 by Lostinet

For example, today sells 2 bread, the price is 3 yuan, then the sales table should also record the then price. And not just the model of the bread, because the price of bread will change.
The key is to have a clear understanding of the meaning and relationship of the data. The record itself is not redundant and does not conflict with the theory of database design.

In terms of the name of the customer, the data of the order record can be regarded as the "customer" at the time of the order, which has different meanings to the "current name" of the Customer table, which is not redundant.

#2楼 Reply Reference View

2005-05-08 14:25 by hehe

The paradigm principle is used to guide the design of the data storage structure, but only the theory. It is necessary to reverse the paradigm, or tolerate a certain amount of data redundancy, so that the performance or logical clarity of the system is better.

Paradigm is a way to optimize data structures based purely on the amount of storage that is stored, and is not a very common thing.

Other than that:
"1 I don't want to delete a payment clause after the order has been released, which makes it impossible to know the" real payment terms ", which is certainly unreasonable.
2 I do not want to, because under this order, and "strict control" payment terms of the "delete" function, which is unreasonable, why can not be deleted? The "terms" will never be adopted next month. ”

In this case, a warehouse-based approach to data management can actually be considered: that is, all data can be divided into usable and unavailable, placed between different tables, and then moved to move the data.

You said the situation is very realistic, from a business perspective, to optimize the data structure to the highest storage efficiency is meaningless, the paradigm is more a guide, rather than the principle.

#3楼 Reply Reference View

2005-05-08 14:39 by cold Maple Day injury

I think this is the difference between theory and practice, or that the general people's understanding of the paradigm is not right.

Generally speaking, the paradigm is a guiding principle, and the paradigm provides a guiding basis for the elimination of the final redundant data. In practice, however, redundant data is not illegal or disallowed, and in many cases redundant data is required to achieve referential integrity in addition to performance requirements.

One of the most important concepts of database design is referential integrity, a complete database, stored in the associated information, should either exist or disappear, as the landlord said:
"I don't want to delete a payment clause after the order has been released, which makes it impossible for these orders to know the" real payment terms ", which is certainly unreasonable. ”
Theoretically, if the referential integrity of the implementation of the paradigm requires that all orders associated with the payment terms here be deleted, otherwise the terms of payment should not be deleted.

However, more importantly: the landlord said that "payment terms" and "orders" should be a weak coupling relationship, they should be separate. This contradiction produces two tables that are associated with the storage mode. In this case, the terms of payment should be used as a secondary table, it is possible that the table is only to provide a drop-down list or other means of convenience input interface, then the "payment terms" are essentially only stored data, it and "order" should not have a relationship.

The landlord's approach from this point of view, does not violate the third paradigm. The third paradigm should be focused on the tables that have a business relationship, and should not include the auxiliary tables.

#4楼 Reply Reference View

2005-05-08 14:56 by Hudan

Basically agree with the landlord's point of view, but on the landlord's example to talk about my thoughts:
As you said, my approach is to add a column in the "Payment terms" basic table to delete the identity column, delete the time to modify the "Delete identity" column, the user will not see this when the order is placed, but query the historical data can still query the previous terms.
For you to say the 3rd "system in the order of how to correspond with the paper orders at hand," my practice will not be able to achieve the corresponding. All the revised terms will be displayed.

In some cases, I think it is worthwhile to have several fields redundant in order to improve the query speed.

#5楼 Reply Reference View

2005-05-08 15:07 by Mikespook

"Kaleby pirates" has a sentence: "The code, more like a guide, rather than the guidelines ..."

#6楼 [ landlord ] Reply reference View

2005-05-08 15:21 by listening to Tangxia. NET

@ Cold Maple Day injury:
I think this also refers to the association, because we can also adopt the third paradigm to achieve, but orders in this case will encounter a lot of problems.

In the design of the order, I will generally have "terms id", "terms name", that is, I will be the primary key corresponding to the name also brought over, the reason to bring in the "terms ID", mainly because the order needs to be modified, when modified, you can default to check the original value. and the "terms of the name" brought in is the reason I said earlier.
Thus, from this point of fact, these are indeed contrary to the "third paradigm".

#7楼 Reply Reference View

2005-05-08 18:17 by Lay

Oh, I think the landlord to lift the example is not a violation, the dictionary table is relatively special. Ultimately, it depends on the specific business.

#8楼 Reply Reference View

2005-05-08 18:58 by the hill to cut wood

"1 I don't want to delete a payment clause after the order has been released, which makes it impossible to know the" real payment terms ", which is certainly unreasonable.
= = Why should I be allowed to delete payment terms? Can't you set it to fail?

2 I do not want to, because under this order, and "strict control" payment terms of the "delete" function, which is unreasonable, why can not be deleted? The "terms" will never be adopted next month.
= = can not be deleted or can not be deleted, the provisions come down? Likewise, is it not possible to set it to fail?

3 I also do not want to, after the payment terms have been modified, resulting in all previous orders to adopt this payment terms into a new term, that the order in the system and the paper orders at hand to correspond again, this is certainly not reasonable.
= = Can the terms of payment be changed freely? Similarly, you cannot create a new condition if you need to modify it?

I think the design of your database is very unsuccessful. Because of the reasons above, you're causing thousands of records to be redundant? Does that sound reasonable?

One already sees, only bears the limited liability ...

#9楼 Reply Reference View

2005-05-08 19:26 by good

In fact, the landlord said very reasonable, a lot of times, we should be due to things and should not be able to live Sili, but a theory exists there is a certain truth, at least for a period of time, so I would like to apply the third, or necessary

#10楼 Reply Reference View

2005-05-08 20:12 by Red Shift

Can be designed like this

Order Form:
Order number (primary key)
Terms number (foreign key)
[Other content]

List of terms:
Terms number (primary key)
Valid tag (bit)
Terms Content
[Other content]

Only the terms that are effectively marked 1 in the Terms table are listed when the order is listed. The deletion of the terms is subject to the term no longer used (in the order form). If someone uses it, just set the valid marker to 0. When the order is deleted, the corresponding terms in the Terms table are checked, and if it is invalid and no one else has used it except this order, the clause will be deleted by the way.

#11楼 Reply Reference View

2005-05-08 20:29 by a Chuan tobacco

I am engaged in a 8-year business system, from a standalone program to a large system of more than 800 tables.
My feeling is that redundancy is really necessary.
Space in exchange for efficiency.
I have a check table for the catering system.
Including the opening of a single member
Open a single point of sale
Open a single meal section

A statement clerk
Statement Point of Sale
Statement Meal Section

#12楼 Reply Reference View

2005-05-08 20:36 by a Chuan tobacco

My feeling is that redundancy is really necessary.
Space in exchange for efficiency.
I have a check table for the catering system.
Including the opening of a single member
Open a single point of sale
Open a single meal section
Open Single Terminal
A statement clerk
Statement Point of Sale
Statement Meal Section
Statement Terminal
These are foreign keys, and each foreign key description includes three descriptions in Chinese, English, and other languages.
When displaying data to the customer, the corresponding description must be displayed according to the current language. In this way, n tables return data, a slightly-sized restaurant with thousands of odd characters per day. Such queries are very efficient and underground.
After redundancy, we have 8 of the above fields redundant to 32. Throw all the description to the check table, the query efficiency doubled. and simplifies the development of programmers.
For the deletion of the set table, my view is why should I really delete it? Setting the primary key of a table can be completely designed to be invisible to the user. The user can still delete, but the real data is just a delete tag.

#13楼 [ landlord ] Reply reference View

2005-05-08 21:27 by listening to Tangxia. NET

@ Climb the hill to chop Wood:
In fact, I really do not want to discuss these issues with you, you say a few of what I mean is not the same thing. You can use "whether effective" to achieve, you mean that all the foundation has "is effective", can customer is not like this "is effective" sign, "is effective" in the customer "temporarily do not" the case will be adopted, know what is called "temporary"? Moreover, a long time you engage in so many "invalid" how disgusting ah.
And I said is can allow customers to delete, you why do not let customers delete, you think this is for customers to consider it?
Let yourself be enlightened.

#14楼 Reply Reference View

2005-05-08 21:52 by RIPPER

The customer must see this effective mark directly, he does not like to see, you can not let him see, this is very easy.

To play customers not to be played by customers, he said to delete you really deleted a record from the database?

#15楼 Reply Reference View

2005-05-08 22:10 by a Chuan tobacco

My word is to agree to make a mark, for the user, what is called deletion? The interface is not visible or deleted.

#16楼 [ landlord ] Reply reference View

2005-05-08 22:25 by listening to Tangxia. NET

I know that you can make the mark, I am also for some need to use the "mark" I will use, but if those who do not need to use, all use the "mark" way to deal with, I feel very uncomfortable, obviously no longer usable, in the database is put, too disgusting. And the trouble of inviting the program.

#17楼 Reply Reference View

2005-05-08 22:40 by Xiao Lu

I think the biggest problem with redundancy is that it is difficult to synchronize and occupy space is the second.

#18楼 Reply Reference View

2005-05-08 23:01 by Chu Xiao

After the actual project, I understand the reason that the landlord said.
It is also feasible to do a deletion mark, but a lot of time, no landlord said the method is good.

#19楼 Reply Reference View

2005-05-09 00:12 by Yfmine

To:hudan
Personal opinion, in the database only add new rules, can not change the existing rules, any changes are considered as new additions, so you can achieve the 3rd. But this kind of data also ...

#20楼 Reply Reference View

2005-05-09 00:57 by Wljcan

Agree with the idea of cold Maple day injury.


From the description of the text, it should be weak coupling, but this passage has some problems:

In the design of the order, I will generally have "terms id", "terms name", that is, I will be the primary key corresponding to the name also brought over, the reason to bring in the "terms ID", mainly because the order needs to be modified, when modified, you can default to check the original value. and the "terms of the name" brought in is the reason I said earlier.

Now that you have the name copy coming up, why do you want the ID? "Default Check original value" does not seem to be true.

In addition, there is no need for technical discussion to have such a strong gunpowder. =

#21楼 Reply Reference View

2005-05-09 08:56 by old fin and cold

The Customer Information section of the order in this article must be copied to the order form. So many people consider the question, how come no one considers it from the legal effect? To make a system to ensure that the first is the unity of the present, after an order entry, as long as there is no modification, no matter how long, its output format and content can not be changed. This is a legal sense of completeness. Can not be the order today is XX Company, tomorrow xx company renamed XXX Co., Ltd., and XX Company's order (or contract) will automatically become XXX Co., Ltd.?

#22楼 [ landlord ] Reply reference View

2005-05-09 09:15 by listening to Tangxia. NET

@ Old wings and cold:
I'm talking about what you mean, an order has been determined, so it's independent and historic.

@ Climb the hill to chop Wood:
The customer said is the reservation, then you certainly retained, I did not deny this ah, but is afraid the customer said not to retain, but you have to keep AH. As for data redundancy you see below.

As for "Why do I need an ID?" "It is possible to make changes, then if you do not have the ID in the past, how to select the default value when modified, if not by default, the result of customer modification is only to modify the other properties, and this ID may be unnoticed in the case of the modification. Because it's a drop-down box.
As for marking, it is also a way, such as the customer said to make a mark or this value should have "temporary" situation, then the logo is of course the best.
Like the old fin and the cold said, an order is a kind, in the actual business in the clear existence, if legally also has the law, even if there is no legal, we should also consider it as an independent, a friend said, what to do after the change? An order was issued at that time, it has the real-time and historical, it is impossible because of your subsequent changes in the underlying data resulting in the loss of the original properties, so the so-called "redundancy" is only from the "third paradigm", and in the actual business, in fact there is no so-called "redundancy."

#23楼 Reply Reference View

2005-05-09 09:39 by NA

If you want to isolate, there is still a lot to isolate oh ....
Why not delete the terms and see if they have already used this clause?
The underlying data is not deleted if it has been used.

#24楼 Reply Reference View

2005-05-09 09:41 by the hill to cut wood

Agreed to the upstairs.
==================
I am not hurt, more than you mean people.
Some people agree that because they are less experienced than you are, at least in terms of management, your view is more inappropriate.
As the basic data, the permissions of the modification and deletion are relatively high, especially the basic data related to other data, which can not be arbitrarily modified and deleted after use, which should be banned at the system level. The system level cannot be solved, it should be resolved at the management level.
If you design like this, then the rest of the basic data will not be brought into another table? Product data, supplier data, customer data, .... Wait, wait. Do you think this data design is successful?
You don't think you can tell me, at least you think it's hard to convince me, I think your idea to do demand analysis is definitely a failure. The final product will certainly be a n unlike a product. Understand the system design there is nothing to do, casually find a few people will do. The customer is the boss anyway. The fault is also the customer's own search.

#25楼 Reply Reference View

2005-05-09 09:56 by Xiao Lu

According to the description in the article, the order is recorded, it is necessary to preserve the immediacy and historicity of the time, so the retention of this data is not called redundancy, as this information is necessary. Therefore, in principle, it does not violate the third paradigm.

The key is how you keep it. Since the name has been copied over, id I think is redundant, and the two data may be inconsistent, if you need to modify later, the direct display name is. If you want to facilitate user input, you can use the input + selection method.
Another option is to keep each version of the configuration, whenever the user modifies or deletes a configuration data, instead of actually making changes, instead of adding a new version, which is best, in full compliance with the third normal form.

As for the customer request to delete, I understand is: the customer can not see the deletion, there is no need to really remove from the physical.

There is no principle of "have to violate", to do something that violates the principle must have more powerful principle as the backing. For example: Appropriate redundancy can improve the efficiency of operation, reasonable redundancy can increase system fault tolerance. If simply to simplify the development process, my experience is: no redundancy development is the simplest.

#26楼 [ landlord ] Reply reference View

2005-05-09 10:20 by listening to Tangxia. NET

rUK
The ID can be considered for non-retention, and it is true that name can be modified directly because, in a sense, the reference data is copied, and it is thought to have been determined.

As for the removal of controls, some of the underlying data is required to use "control to limit", but not all. Why do people not think about some of the differences in reality?
Na says there's a lot to isolate?? Also not ah, with independence and historical in kind, is recommended to isolate.

Go up the hill to cut wood to say so many people are inexperienced??
I do not know, is not very experienced people like you, all the basic data are used "Delete control" to do, you can go to investigate.

#27楼 Reply Reference View

2005-05-09 10:37 by [email protected]

The deletion of the main table is, of course, best done with markup.
What if the customer needs to use the deleted data later?

#28楼 [ landlord ] Reply reference View

2005-05-09 10:41 by listening to Tangxia. NET

@[email protected]:
Yes, if those need to be "temporary", it should be done with tags. You should be able to modify the status or delete it if they really want to delete it.
In fact, this deletion does not matter, the problem is that the underlying data should be copied past.

#29楼 Reply Reference View

2005-05-09 10:59 by Laser.net

I personally agree with Lostinet's view that it is not a violation of the third paradigm.

What you store in the "order form" is "the customer information for that order" and "the payment terms for that order", which makes no sense to leave the order, so they depend entirely on the order's primary key, and that order needs that information, which is essential to the order in the context of the business you are talking about. So this would have been in the third paradigm:)

#30楼 Reply Reference View

2005-05-09 11:02 by Austin Leng

It makes sense to ask listen to Tangxia. NET , the number of fields in a table if too many, you say there is no problem, such as a table TestTable, which contains more than 60 fields, you think there is no problem?

#31楼 Reply Reference View

2005-05-09 11:05 by Austin Leng

Database design, alas, very important, which cattle people recommend a cow book, thank you

#32楼 [ landlord ] Reply reference View

2005-05-09 11:15 by listening to Tangxia. NET

In fact, from the business point of view, the copy of this data is not a violation of the third paradigm, that a main table may have a considerable number of associations, it is also based on the information of the main table, such as orders, those inherent in the order itself should be copied from the base table, and for the "order type" May not belong to the order itself objectively the attribute, then we can adopt the foreign Key association way. As such, there is a need to limit the deletion.

#33楼 Reply Reference View

2005-05-09 14:50 by arming

We are talking so much, I think there are basically two views: 1 is copy. 2 is a foreign key reference, but is controlled by the delete flag.  
The first surface does not violate the third law, because it can take into account changes in terms. Cold maple days hurt and listen to the Tang. NET has said the corresponding reason.  
The key here is the "terms", the old fin and the cold also clearly put forward the terms of business meaning. Then the terms information is not a simple data dictionary business can cover. Where the terms of the information maintenance and order reference clauses must have some special code to reflect this particularity.  
by direct copy to ensure that the "legal" order and new orders to use the latest terms, can really save the code, but the data redundancy is undoubtedly, imagine, a 2000-word agreement, each order has a copy, and I this clause in fact, two or three years a change.  
If you introduce a version concept to the terms information, or whether the concept is enabled (a point of view on the mountain chopping wood, but his tone is really bad, not the attitude of the discussion), there really should be such a concept, (note that not all citation information is so maintained.) such as Sex). Once a clause is referenced by an order, it is not allowed to be modified or deleted and can only be changed in version change mode.  
the deletion of invalid terms (too many really uncomfortable), can be controlled in the terms of maintenance business, such as showing how many orders are currently quoted, but this is only a usability issue. Can be implemented through enhanced interface control.  .
What terms can be used for a new order, which is also a specific business decision, I think the interface on how to display, dropdown box, or pop-up window, should not be discussed in this issue. A static method is typically provided in a terms collection class that extracts the appropriate set of terms as a choice for a new order.  
Whether the terms can be re-selected when an order is modified, or if the terms have just been updated, and whether the new terms are automatically enabled, are determined by the specific business and not the issue.  

BW: I do not like to be constrained in design or development. Everything depends on the circumstances, if the project is small, or time tight, insufficient resources, or the term length is not long, I will not hesitate to use copy.  
But if the terms really grow to make me feel redundant and later maintainable. Really should take the time to think about how to design terms information to maintain the business.  

#34楼 Reply Reference View

2005-05-09 15:30 by step

I encountered the application also used the redundancy, at that time felt that does not conform to the paradigm requirements, always reluctant to agree, today finally convinced. The mentality that is always bound by the theory is very uncomfortable ah!

#35楼 [ landlord ] Reply reference View

2005-05-09 15:38 by listening to Tangxia. NET

@Arming:
Your opinion is roughly the same as ours, but you may not understand what is called the "payment terms", the English name "Payment term", it is not a 2000-word agreement, just stating 30 days payment or 60 days payment.
Let me show you a picture:

#36楼 Reply Reference View

2005-05-09 16:46 by RIPPER

Payment term is translated here to make the terms, but also to understand?

Term
N.

1.
A. A limited period of time.
B. A period of time is assigned to a person to serve:a six-year term as senator. See synonyms at period.
C. A period when a school or court are in session.

2.
The following slightly ...

Your l10n is so rotten that you have to advise the boss to quit him:)

The "third paradigm" that the database design "sometimes has to violate"