Đang chuẩn bị nút TẢI XUỐNG, xin hãy chờ
Tải xuống
Tham khảo tài liệu 'sql antipatterns- p6', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả | Objective Tidy Up the Data 251 22.1 Objective Tidy Up the Data There s a certain type of person who is unnerved by a gap in a series of numbers. bug_id status product_name 1 OPEN Open RoundFile 2 FIXED ReConsider 4 OPEN ReConsider On one hand it s understandable to be concerned because it s unclear what happened to the row with bug_id 3. Why didn t the query return that bug Did the database lose it What was in that bug Was the bug reported by one of our important customers Am I going to be held responsible for the lost data The objective of one who practices the Pseudokey Neat-Freak antipattern is to resolve these troubling questions. This person is accountable for data integrity issues but typically they don t have enough understanding of or confidence in the database technology to feel confident of the generated report results. 22.2 Antipattern Filling in the Corners Most people s first reaction to a perceived gap is naturally to want to seal the gap. There are two ways you might do this. Assigning Numbers Out of Sequence Instead of allocating a new primary key value using the automatic pseudokey mechanism you might want to make any new row use the first unused primary key value. This way as you insert data you naturally make gaps fill in. bug_id status product_name 1 OPEN Open RoundFile 2 FIXED ReConsider 4 OPEN ReConsider 3 NEW Visual TurboBuilder Repor erratum this copy is P1.0 printing May 2010 Antipattern Filling in the Corners 252 However you have to run an unnecessary self-join query to find the lowest unused value Download Neat-Freak anti lowest-value.sql SELECT b1.bug_id 1 FROM Bugs bl LEFT OUTER JOIN Bugs AS b2 ON b1.bug_id 1 b2.bug_id WHERE b2.bug_id IS NULL ORDER BY b1.bug_id LIMIT 1 Earlier in the book we looked at a concurrency issue when you try to allocate a unique primary key value by running a query such as SELECT MAX bug_id 1 FROM Bugs.1 This has the same flaw when two applications may try to find the lowest unused value at the same time. As .
