It's a blessing and a curse.  There is never a dull moment and always something for me to do.  I've acquired many skills out of necessity.  One of those skills has been database administration.  More specifically, Oracle administration.  I've learned to hate the database server.  When something goes wrong in that box of mystery, everything grinds to a halt and people start getting pissed.  So, in no particular order, I'd like to share some nuggets of information I've learned over the years.

• Cheap RAID cards are a bitch.  I've had the opportunity to witness the carnage that can happen when good RAID goes bad.
• A fast disk is a fast database.  Unless you can keep your entire database in memory, your data is going to need to be read from disk.  Disk IO is a major bottleneck.
• RAID is awesome when it works.  What's faster than one fast disk?  More fast disks and a fast RAID controller.  More is better. Just make sure you invest in quality equipment.  Good RAID controllers aren't cheap for a reason.  The expensive ones don't corrupt your data.
• RAID 10 is better than RAID 5.  The only exception is if you never need to write data with any speed.  Disks are so cheap that it doesn't make any sense to try and save the money.  RAID 10 is just that much faster.
• Indexes are a double edged sword.  They are necessary for fast queries, but too many will slow inserts and updates.  Also, indexes on large tables should be chosen carefully as the indexes can consume quite a bit of disk.
• A bad query can bring the database to its knees.
• Locks protect multiple requests from stomping on top of each other.  They are also good at stopping inserts and updates from happening when one gets stuck.
• Keeping the statistics up to date makes sure that the database choses an optimal execution plan for retrieving your data.  The database needs to know how your data looks. 
• Full table scans aren't always bad, but most of the time they are.  The only time they aren't is generally when you are querying a majority of the data in the table.
• Be careful where you store your LOBs. Storing LOBs that you don't always need access to inline with other data can be slow.  It reduces row density per data block which means more disk IO is needed to get your data.
• There are about 42 different ways to query the same data.  Most of them are terrible.

Obviously you should take this information for what it is.  This is just a few things I've come to believe based upon 5 years of banging my head against a wall.  I write software, and somehow I just inherited this database responsibility.  I'm not sure how, I think I was just closer to the server when it died the first time.  

Most of the problems I've had with databases have been hardware related. A good backup strategy goes a long way to keeping your ass out of the fire.  It's a terrible feeling when you lose your own data.  It's an even worse feeling when you lose someone else's data.  If you are an accidental DBA, I feel your pain.  We should all start a support group.

First, I should describe my background. I work for a national PPO healthcare network. We directly contract with medical providers and also serve as a PPO aggregator taking in data from several sources. We take data from other networks, wrap it up alongside our own, and then redistribute the whole shebang to clients. Multiple gigabytes of data flow into and out of systems every month.

Taking data from other sources can be difficult. The burden is on the recipient to "scrub" the data and make it fit into your own formats and standards. The only data that you can really trust is your own. There is only so much that you can expect the sender to do as they already have the data in the format that they need. Basically the sender needs to do two things in order to make a successful exchange possible.

1. Provide a unique record id. This is absolutely critical.  Some clients I've dealt with don't have truly unique record level identifiers.  Sometimes a composite of various fields can be used, which is perfectly acceptable but not exactly ideal.  When there is no identifier, then it becomes really hard to know when data changes.
2. Provide a consistent and reliable feed of data. The data that is being provided needs to updated regularly and be in a consistent format.  A field that you said was numeric can't magically become alphanumeric without something screwing up. The worst case scenario is when data gets changed in a subtle way that somehow gets misinterpreted on the other end without someone catching it.  When you are updating millions records per month, you can't eyeball very much of it.

That's basically it for the sender.  I told you the burden was on the recipient.  So, you have an outside source of data, now what?

1. Uniquely identify the source.  When you are looking at a piece of data, you have to know where it came from.  Each source of data into your system (including your own) needs to have a identity.  That identity has to be tagged to the data feed.
2. Store the source's unique record id beside yours. Before you even start to think about it, no you can't use their identifier as your own.  Furthermore, you shouldn't try to generate some kind of composite key based upon the source and the source's id. You need your own internal id.  Anytime I have a mashup of data, I create an identity (id) for foreign keys and then store the source (source_id) and the source's record identifier (external_id).  External id is always an null-able column with varchar(max).
3. Reformat the data to fit into your system. Your data standards are not the responsibility of the source.  That bears repeating, your data standards are not the responsibility of the source.  This will mean that you will have to make compromises with the data and sometimes possibly outright reject data.  It's just part of it, but you'd rather have less higher quality data, than a bunch of crappy data.
4. Notify the source of problems so that they have the opportunity to make corrections. While I don't like it when it happens, clients will approach me from time to time to point out oddities within my own data.  It's going to happen, and it's only because they have a different perspective on the data and may be looking at different things.  More eyeballs on the data means better quality data in the end.
5. Trust nothing that you are receiving. Whenever the data exchange discussion is initiated, try to get as much information from the source as possible.  What fields are required?  What data types are expected?  Take all of the information you gather and write code to enforce it. The better job you do here goes a long way in catching senders who violate #2 in the list above.
6. Use the source's naming conventions.  Somewhere you will be doing a mapping from their data to your own.  When you are referring to their data, name it (in code, in a temporary table, wherever) what they call it in their documentation.  This makes debugging stuff later on so much easier.  For the love of all things holy, never ever refer to their data by position within a  file. Whenever their format changes (sometimes it will happen), then you will be up shit creek. I name it what they call it right up to the point that it leaves their data structures and enters my own so that I can explicitly see the mapping.  "my.StateDiscount = their.Amount-their.FsAllowedAmount" goes a long way in bridging communication barriers 10 months down the line when an odd problem shows it's ugly head.

Bonus Tip: I've found that full data exchanges much more user friendly than add/update/delete notifications.  This is from my experience with data that is relatively constant with a lot of random updates, which may not necessarily apply for other type of data (time sensitive data comes to mind real fast here).  With relatively static data, a full exchange provides a few benefits for both sides.  The sender doesn't have to worry about tracking changes.  The recipient doesn't have to worry about processing the data in a certain order.  The latest data is the only data that matters.  It also allows the recipient to easily detect if a record gets orphaned somehow.  With change notifications, there is really a need for an acknowledgement from the recipient so that the sender knows the data was processed and is up to date.  The only downside for the full exchange can be file size, but it's a trade off I find acceptable.

That's about all I have on this subject.  If you have any more tips on exchanging data, I'd love to hear them.  This is a big part of what I'm responsible for every day.  A lot of it is common sense, and a lot of it is just stuff I learned by screwing up.  Dealing with other people's data is never fun.

My wife and I are currently living in a small house.  It's suited us just fine and will continue to serve us well over the next few years. Even so, getting a room ready for baby has been, well, a chore.  You see, what is to be this child's room was once the spare bedroom/junk room.  I didn't realize how much crap that one could acquire in 5 years.  We've been going through this junk and deciding what to keep and what to toss.

The room is emptied, now what?  Well, it's time to paint. If all goes well, that will be done tomorrow.  I'm sure it won't since I didn't get near the prep work done that I wanted to do tonight. Regardless, this weekend is going to be it. Then  we'll need to assemble the furniture that we spent the past several weekends shopping for, buying, waiting on delivery, and picking up.  Finally, we'll be decorating the place in stuff that is "cute" and "cuddly" which are two words that I'm not too accustomed to. Finally, after we've gotten the baby penthouse done, then it'll be stocked with diapers, ointment, wipes, snot suckers, and whatever else it is that is needed for the little one's scheduled maintenance and servicing. 

image courtesy of michelleannb

What you don't realize is that all of this is just a distraction.  You see, noob parents like ourselves do all of this excess preparation to keep our minds off the reality of "OH SHIT! I'm about to be a parent and I have no idea what to do!"  It's not that it feels scary, or at least that emotion hasn't hit me yet. Right now, I feel like I need to provide the baby with the best and safest environment I can provide.  My awareness is heightened regarding my family's overall safety and well-being.   

The people around me have been very encouraging.  I've received quite a bit of advice for everyone. When I stop to think about it, I can put my worries aside and realize that this is an absolute blessing.  It is going to be amazing to see this child grow and develop into someone who is like me in ways yet unique in others.  I look forward to guiding him to being a responsible person who contributes to society.  I can show him my values and morals.  I can teach him my knowledge.  In the end, he'll take all of that, and make his own life out of it in his own direction. 

I have no idea what the future holds for my wife, my unborn son, and myself.  What I do know is that I'm prepared to handle whatever is coming.  Look out world, version 2.0 is coming.  I can't wait to meet this little guy.

This code is provided as is. I'm not making any guarantees that it's 100% right and I'm not responsible if anything bad happens as a result of your use of this. So, don't yell at me if small animals are harmed with the use of this code.

--Returns 1 if valid, 0 if invalid
CREATE OR REPLACE FUNCTION valid_npi (npi in varchar)
RETURN number
IS
    tot NUMBER := 24; --80840 prefix for npi
    val NUMBER := 0;
BEGIN
  if(
    npi is null or
    length(npi) <> 10 or
    LENGTH(TRIM(TRANSLATE(npi, '0123456789', ' ')))>0 --not numeric
  ) then
    return 0;
  end if;

  for i IN reverse 1 .. LENGTH(npi) loop
    val:=SUBSTR(npi, i, 1);
    if mod(i,2)=1 then
      val := val * 2;
      if(val > 9) then
        val:=val-9;
      end if;
    end if;

    tot := tot + val;
  end loop;

  if(MOD(tot, 10)=0) then
    return 1;
  else
    return 0;
  end if;
END valid_npi;

Instead of turning this into a rant, I want this to be a beneficial post aimed at bridging the gaps that exist between us.  I've had various people compliment me on my ability to make others understand things.  I'm not an expert, but I do feel like there are a few things that help me make known the things that I want known.   

1. Engage yourself in the topic for the moment.  The absolute first thing that needs to happen is that the topic have your undivided attention while you are expressing yourself.  Multitasking is a horrible lie created by upper management to try to squeeze more work from people.  The truth is that I've never seen anyone that can do two or more things simultaneously and do any one of them with great skill and accuracy. If you want to get your point across well, you can't be thinking about a hot date tonight or worrying about your marriage.  Put that aside for 5 minutes and focus on the topic at hand.  
2. Understand who you are talking to. By knowing your audience, you will be able to quickly make your point.  For instance, if I am talking to a non IT person, I will switch my vocabulary from entities to things, and from tables to buckets.  Getting your point across isn't a lesson for the audience in your area of expertise.  If you are talking to someone stupid not so bright, then use smaller words. (For once I'm not trying to be mean, I promise!)  If you are talking to someone who has a hobby, then try to use references that relate back to what they know.  Just try to find some common ground.
3. Look to your audience for signs of confusion/lack of interest. This is a lot easier to do in person that over the phone or by email.  Read into the responses that you are getting to make sure that you are being understood.  If people's eyes are glazing over, then you know that they aren't understanding you.  If they are just disengaged, then call them on it and bring their focus back to you.  If they are trying to be engaged, but just don't understand, then change your explanation.  With any communication medium, watch out for unintelligent responses for the topic at hand.  If I'm explaining some business process to a salesperson and they respond about the fax machine not working, then I know I've lost them.
4. Be respectable.  Your audience needs to have some sort of confidence that what you are saying is trustworthy.  In order for your audience to trust you, you need to know what you are talking about and somehow convey that to the audience.  This one is hard for me to quantify.  All I can tell you here is what NOT to do.  Don't use net-speak (LOL, ROFL, FTW, etc) and don't abbreviate like people tend to do in texting (R U OK 2?).  Don't use slang.  Don't mumble and don't stutter. I'm sure there is more here, that's not the point. You just want to make it easier for people to respect what you are saying.  
5. Know what you are talking about. I saved this one for last, but it's far from the least important.  You can't effectively communicate something which you don't understand.  Period.  You may be able to BS your way through some scenarios, but people are able to see through most of it.  In order to be an authority on a subject, you have to know it in and out.  If you know what your talking about and the audience knows that you know what you are talking about, then they will be more engaged to what you are saying.  Getting undivided attention from someone is half the battle to getting your point across.

 As a software developer, I'm only one part of the IT umbrella and I can't speak for the entire industry.  With that said, it's my opinion that software developers are naturally good communicators.  We have to study problems and understand them intimately in order to turn ideas into work-flow and business decisions into code.  We have to write code in a clear and concise manner and then document the confusing parts.  Code is a communication medium.  Fortunately for us, the compiler tells us when it doesn't understand. People aren't so easy.

Management may complain that it's hard to understand us, yet I've spent my entire career (All 5 years of it! I'm still fresh.) trying to understand them.  In the end, we work problems from different angles and we have to meet in the middle somewhere to understand one another. If I can understand about your book of business and your pipeline, then you can learn about my web services and my databases.  You don't need to know how I implement things, just know the fundamentals about what they represent.  

You and I can all be better communicators.  It just takes a little willingness to understand what and who are around us.  Once we accept that we're not alone, and that the people around us are indeed different, then we understand that there is no one-size-fits-all answer to sharing knowledge.  Each person or group requires different technique, but everyone requires effort.  Just be genuine and truthful, pay attention, and the rest will follow.