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?"

Requirements vs Architecture

drsql — Mon, 13 Apr 2009 01:30:12 GMT

Okay, so on the first look this sounds like the most boring Japanese action movie ever. Requirements is tearing through the village, and Architecture is in the city. Developers by the horde are trying to code both of these into oblivion…Maybe not. Clearly I am talking about something a little more exciting…the battle between the forces of business and computing, or more precisely, the forces trying to keep them apart. Back in 2000, C J Date produced a book (which I clearly haven’t read) called “What, Not How”. I haven’t read it because as a person who does implementation, my primary care is about the opposite. “How, Not What” I would like to think that if the “what” was perfectly defined, all we as implementers would have to do would be to translate from business language to machine language, but all to often I find myself involved in the business analysis, either because it is thought that I would be, or because the requirements come to me in terms of how the software will be created (which isn’t usually the way I want it done, which usually does matter, since it is my responsibility to actually design and often do the implementation.)

The thing I tend to find is that one of the most difficult parts of the software implementation process is getting people in the proper roles. As an architect, I find that a lot of times it is expected that I know a lot about the subject matter of the system I have created. Even worse, when it comes to the creating a new system, it is supposed that I would be able to pick a lot about the system that is being designed (often without it being formally communicated.) There is some truth there, but let’s be honest. I got a degree in Computer Science, not chemical processes, accounting, or food service/logistics. Every new system that I might work on is a crash course in learning a new industry. And to be honest, not always one that I really want to fill my brain with permanently.

However, the person whose role is entitled Business Analyst does in fact have that role to learn enough about a problem in an industry and turn it into a specification. This document is what will be used to create a future piece of software and/or manual processes (that is for us to decide once the scope of the system has been decided upon. Their job is to, well, this might sound silly if you think about it, but to analyze the business and “figure it out”. This role is one that I feel is essential to the health of a project because they are cleanly divorced from the implementation whereas I as an architect are not.

Put it this way, consider the role of the architect for a building. Does the architect determine what the house is for? No, for if they did every house would be 100 stories with artsy windows in every room. I saw a good example of this on the TV Show “How I Met Your Mother” the other night. Ted (the architect) was told to design a bleak room for the clients new “firing room” (this was to be a copy of the existing one, just on a different floor of the building). He wanted a horrible place to can people because he was completely sadistic. Ted wanted to put his touch, and designed a whole system for nicely letting someone go, including a sympathy room with councilors, and such. Needless to say he was fired from his job.

As an architect, it is my role to take the business requirements and negotiate a solution that closely resembles what is desired, bending mostly for cases where the desired system, if built in the exact manner of the requirements, would extinguish life on Earth or cost too much (sometimes just the latter.) Keeping out the influence of implementation during the requirements gathering phase of a project eliminates the “this is how you do it in this technology” creep. I am a relational designer, and as such I feel I can do anything in SQL (I want someday implement a compiler, much like I used VB to implement one in a college class once. Not my best grade, but it did work.) I constantly find myself steering requirements toward what *I* know how to do, and as such find that I tend to miss a few points because I have started designing/coding mentally even before all of the requirements have been gathered. Of course, in the implementation it is okay to steer towards what you know, as this is how things get done. Having multiple strong minded people on a team, however, is how great things get done.

A classic example of this for me was when I was architecting the db for a chemical plant product testing system. Materials had to be within a given range or they were considered not shippable. Cool, makes sense, keep getting requirements…must…get…coding. Well, a person who had really thought about their business would have probably realized that “not shippable” might not always mean “not shippable” when you are talking about materials that are not medical nor edible. So in the first week of production they go, “okay, so how do I override these requirements to ship this lot of product to client X?” Oops. Maybe if I had spent more time considering the entire process rather than trying to finish up my diagram to generate code…

What do you think? Can you turn off the “Get ‘er done” part of your mind when you are doing requirements, or do you find that you really really want to start building software well before you really understand the entire problem?

Now, before you get all “but in Agile we don’t gather ALL requirements…” understand that I don’t mean ALL requirement for an entire project. What I do mean is as many requirements as possible about the area you plan to implement, and I still mean that if you are the coder that steering requirements toward your expertise is not a great service to anyone but you…well, unless you are really good, then maybe it is okay. In my example, if we had said “We aren’t going to implement overriding in this sprint,” the system would have still been unusable.

Louis Davidson : Database Design, Design, Best Practices, Requirements

What is a physical database?

Requirements vs Architecture