Search results matching tags 'Design' and 'Writing'

Chapter 8–Patterns and Anti-Patterns

drsql — Sun, 10 Jul 2011 17:29:42 GMT

In this last kind of “creative” chapter, I will look at some of the ways you implement common problems in your relational database, and some of the ways you probably shouldn’t. The “should” sections will deal with:

Uniqueness – Beyond the simple uniqueness we have covered in the first chapters of the book, looking at some very realistic patterns of solutions that cannot be implemented with a simple uniqueness constraint.
Data Driven Design – The goal of data driven design is that you never hard code values in your code that don’t have a fixed meaning. You break down your programming needs into situations that can be based on sets of data values that can be modified without affecting code.
Hierarchies – A very common need is to implement hierarchies in your data. The most common example is the manager-employee relationship. In this section I will demonstrate the two simplest cases, and summarize other methods that you can explore
Images, Documents, and Other Files – There are quite often a need to store documents in the database, like for a web users’ avatar picture, or a security photo to identify an employee, or even documents of many types. We will look at some of the methods available to you in SQL Server.
Storing User-Specified Data – You can’t always design a database to cover every known future need. In this section I will cover some of the possibilities for letting the user extend their database themselves in a manner that can be somewhat controlled by the administrators.
Generalization – In this section we will look at some ways that you will need to be careful with how specific you make your tables so that you fit the solution to the needs of the user.

This marks an increase of a 4 sections from the last book, when I added this Patterns chapter. I did take away a few bits about sequence and calendar tables, but I do plan to move this to a later chapter on development, where I will discuss the sorts of objects that I find nice to have in each database and why.

For the anti-patterns, I am adding one more in this edition, on undecipherable data.

Undecipherable data – Too often you find the value 1 in a column with no idea what 1 means without looking into copious amounts of code.
One-size-fits-all domain – One domain table to implement all domains rather than individual tables that are smaller and more precise
Generic key references – Having one column where the data in the column might be the key from any number of tables, requiring you to decode the value rather than know what it is.
Overusing unstructured data – The bain of existances of the dba, the blob of text column that the user swears they put well structured data in for you to parse out. Can’t eliminate a column for notes here and there, but overuse of such constructs lead to lots of dba pain.

This is one of my favorite chapters because it really gets to the core of design. Up until now we have stuck mostly to basics and fundamentals, building very basic structures and working with the object types available to us. Here we build practical solutions to solve common problems.

From here, the next chapter we will move along to security, structures, and then putting the finishing touches on things. Hopefully soon I will have some exciting new about a final chapter that will tie it all together once I get final approval and acceptance from the guest writer on this chapter.

Normalization and How to Know When You Are Done… The short version…

drsql — Sun, 29 May 2011 20:54:15 GMT

A while back, I was working on a short article about Normalization for a book that never got published (admittedly I wasn’t getting paid for the article, and it wasn’t for charity, so I wasn’t that broken up over it.) The task at hand was to, in 2 pages or less, describe the process of normalization and help you to know when you have finished. In my upcoming book Pro SQL Server 2000 + N (where N > 10) Relational Database Design and Implementation, it takes about 45 pages. So it wasn’t really a realistic task, especially considering I have spent about a full paragraph letting you know how hard the task is going to be. The most important thing that is missing from this short introduction is examples, which I include in the book in truck loads.

There are two distinct ways that Normalization is approached. In a very formal manner, there are a progressive set of “rules” that specify “forms” that you are working to achieve. There is nothing wrong with that definition, but progressing through the forms in a stepwise manner is certainly not how any seasoned data architect is likely to approach the problem of designing data storage. Instead, you design with the principles of normalization in mind, and use the normal forms as a test to your design.

The problem with getting a great database design is compounded with how natural the process seems. The first database that the past uneducated version of me built had 10+ tables, all of obvious ones like customer, orders, etc. set up so the user interface could be produced to satisfy the client. However, tables for address and even order items were left as part of the main tables, making it a beast to work with for queries, and as my employer wanted more and more out of the system, the design became more and more taxed. The basics were there, but the internals were all wrong and the design could have used about 50 or so tables to flesh out the correct solution. Soon after (at my next company, sorry Terry), I gained a real education in the basics of database design, and the little 1000 watt halogen light bulb went off…

That light bulb was there because what had looked like a more complicated in the college database class that no normal person would have created (bet you can’t guess what my grade was in that class!) was really there to help my design fit in with the tools that I was using. Turns out that the people who create relational database engines use the same concepts of normalization to help guide how the engine is created that I needed to for a database to work well. So if the relational engine vendors are using a set of concepts to guide how they create the engine, it turns out to be actually quite helpful if you follow along.

First, lets look at the “formal” rules. The normalization rules are stated in terms of “forms”, starting at First Normal Form, and including several others some of which are numbered, some are named for the creators of the rule. (Note that in the strictest terms, to be in a greater form, you ought to also conform to the lesser form. So you can’t be in third normal form and not give in to the definition of the First). To be honest, it is rare that a data architect will actually refer to the normal forms in conversation specifically unless they are having a nerd argument with a developer that is trying to design an entire customer relationship management system in a single table, but understanding the basics of normalization is essential to understanding why it is needed. What follows is a very quick restatement of the normal forms:

First Normal form/Definition of a Table – Attribute and row “shape”
- All columns must be atomic—one value per column
- All rows of a table must contain the same number of values – no arrays
- Each row should be different from all other rows in the table – unique rows
Boyce-Codd Normal Form – Every candidate key is identified, and all attributes are fully dependent on a key, and all columns must identify a fact about a key and nothing but a key.
- Encompasses:
  - Second Normal Form - All attributes must be a fact about the entire primary key and not a subset of the primary key
  - Third Normal Form - All attributes must be a fact about the primary key and nothing but the primary key
Fourth Normal Form - There must not be more than one multivalued dependency represented in the entity. That is to say that every attribute relates to the key with a cardinality of one. Not a common rule to violate, but it definitely does occur.
Fifth Normal Form - All relationships are broken down to binary relationships when the decomposition is lossless. Very rarely violated in typical designs.

There are other, more theoretical forms that I won’t mention, but they are rare to even encounter the definition. In the reality of the development cycle of life, the stated rules are not hard and fast rules, but merely guiding principles that can be useful to help you avoid certain pitfalls. In practice, we end up with denormalization, (meaning purposely violating a normalization principle for a stated, understood purpose, not ignoring the rules to get done faster) mostly to satisfy some programming or performance need from the consumer of the data (programmers/queriers/etc)

Once you deeply “get” the concepts of normalization, you really will find that you build a database like a well thought out Lego creation, desiring how each piece will fit in to the creation before putting pieces together, because disassembling 1000 Lego bricks to make a small change makes Legos more like work than fun. Some rebuilding based on keeping agile can be needed, but the more you plan ahead, the less data you will have to reshuffle.

In actual practice, the formal definition of the rules aren’t thought of at all, but instead the guiding principles that they encompass are. In my mind, I use the following four concepts in the back of my mind to guide the database I am building, falling back to the more specific rules for the really annoying/complex problem I am trying to solve:

Columns - One column, one value
Table/row uniqueness – Tables have independent meaning, rows are distinct from one another.
Proper relationships between columns – Columns either are a key or describe something about the row identified by the key.
Scrutinize dependencies - Make sure relationships between three values or tables are correct. Reduce all relationships to binary relationships if possible.

The question in the title still has yet to be conquered. “How to know when you are done?” What I left out of the description of Normalization was the granularity you go with. The word “atomic” is a common way to describe a table or column that is normalized enough. Atomic would tend to indicate something that is broken down to its absolute lowest form. But unless you are not a nerd (and would you really be reading this if you weren’t?) we know that there are lots of particles smaller than an atom. When you try to mess with particles smaller than the atom, you get a mushroom cloud that even Timothy Leary would not have approved of.

It is the same way with databases. Tables and columns split to their atomic level have one and only one meaning. Deal with them at a higher level, and you will suffer with lots of substrings, switching attributes that you use to find out what a table means in a situation. But break things down too far, and you will suffer even more. My best example of this is a column that holds a large quantity of text. If you never need to us part of the data using SQL, a single column is perfect (a set of notes that the user uses on a screen is a good example.) You wouldn’t want a paragraph, sentence, and character table to store this information. On the other hand, that same character column is abused when the users start putting coded information (because users WILL find a way to work if your software fails them). Then and you have to search for, you will need to begin working with the less comfortable string manipulation functions in SQL… And just try to index a part of a large text column. Possible? Sometimes. Best way to go? Never.

The key to knowing what is normalization and what is an academic exercise for a nerd is to understand the needs of the users (commonly referred to as requirements, as in “Why don’t we ever have good requirements before we code!?!”). If it is clear that the user is planning on maintaining a list of values and will need to update them programmatically, then it is your job to make each value a row in a table. But if there is no requirement to ever search on a value in that list or programmatically access part of the value, then it might be overkill to do anything other than leave the value alone. It is often best to err on the side of caution, but the ideal relational storage for a document would be minimally at the word/punctuation level. If you are read this far and are convinced that would be the proper solution, then you need to get a complete book or take a class on the subject before you start creating a relational database.

The reasonable answer to when you are done normalization is when users have exactly the right number of places to store the data they need and you can query/use the data without parsing it… Easy enough until the user changes their mind, huh?

Design Book–Dimensional or No Dimensional, that is..the question

drsql — Wed, 01 Dec 2010 02:29:31 GMT

So, it is right there in the title of the book “Relational Database Design” etc (the title is kinda long :) But as I consider what to cover and, conversely, what not to cover, dimensional design inevitably pops up. So I am considering including it in the book. One thing I try to do is to cover topics to a level where you can start using it immediately, and I am not sure that I could get a deep enough coverage of the subject to do that. I don’t really feel like it has to be the definitive source on all topics, but it should be usable.

For example, I have a chapter on physical structures, indexes, files, partitions, etc. After reading the chapter, you will have a good enough feeling for the structure of the database to know the basics of applying indexes, partitions, filegroups, etc and a good amount of when and where to apply them. In the end, I usually direct readers to go elsewhere for the truly advanced coverage of the SQL Server Internals (like the books of the owner of the http://www.sqlserverinternals.com/ domain, in fact).

In this case, I am not really thinking that a full chapter is possible, but more of a quick intro. Fact and Dimension tables, and an example or two. The real payoff will be in the case study chapters where I could include a basic set of tables to demonstrate how offloading reporting to a dimensional structure could be done and how it can be used to produce a reporting model that takes the complexity out of writing reports. No ETL coverage of course, and I would use a complete reload script to simulate the data.

Admittedly, the point of these design book blogs is to talk myself into or out of doing something, and usually I pretty much have the answer by the time I am finished. In this case I am still not sure. On the good side of thing, I won’t have to just say “data warehousing, good idea, denormalization, bad idea, get another book and find out”. On the other hand, can I really cover the topic deep enough to make it worthwhile? I don’t think I can include an example in the case studies and not introduce it in the skills chapters.

What is a physical database?

drsql — Thu, 11 Jun 2009 22:26:36 GMT

A bit of terminology that gets beaten to death is that of the “physical” database. I would think most every DBA uses this term (I do), but…to mean what? I think there are two common utilizations:

The layer of tables, constraints, indexes, etc used to store data
The actual on-disk structures.

Frankly, until 3 years ago, I used the first interpretation. However, I was beaten up pretty badly by a few people whom I don’t really remember (I think Anith Sen was one of them.) The problem is, I was scolded, “physical” already had a meaning, given it by the “founder” himself, EF Codd.

So, checking his 12 Rules, Codd stated the following two things:

Rule 8: Physical data independence:

Changes to the physical level (how the data is stored, whether in arrays or linked lists etc.) must not require a change to an application based on the structure.

Rule 9: Logical data independence:

Changes to the logical level (tables, columns, rows, and so on) must not require a change to an application based on the structure. Logical data independence is more difficult to achieve than physical data independence.

And actually, the implementation layer really is the logical model if you follow his terminology since his rules were pertaining to the relational model and not the entire design process. This article says it better than I can in a long blog, but I am not sure about that URL (mac.com?):

http://homepage.mac.com/s_lott/iblog/architecture/C465799452/E20080301143528/

The physical layer of a relational database occurs down at the file system level. Codd's "Rule 8" (Physical Data Independence) says that the things we're designing in ERwin (and similar tools) are the things our application depends on. These are not physical in nature, but are the relational implementation.

So the thing I am trying to say is that physical means that a little 5 volt charge is sitting there representing a bit of data in the physical world. I like the term logical to mean implementation platform non-specific.. The thing in the middle is the SQL Server/Relational implementation specific model. It may take liberties to optimize for SQL Server, but it is not physical. That is were partitioning. indexing, filegroups, etc come in. Changes to this layer ought never be noticable by the application.

I guess in the comments, I ought to expect a good number of replies that might start to answer the question. Does it matter? Is it only semantics? Hey if you don’t think semantics matter, I hope that when you find yourself drowning that the person who has the choice of tossing you a life preserver or a sack of door knobs interprets the meaning of your cry for help in the way you intended. You would hate to find yourself at the bottom of a lake thinking “hmm, I wonder why they did that? Did they hate me, of just mis-interpret the meaning of my sentence?"