DB Designing? Objective /Goal

One of my shop mate came up to me and said – “Hi! I did a database design for one of the projects. I need to set my goal so that my project supervisor assesses me at the end of the exercise.”!

Well, my first thoughts were like what is a non-db guy doing in a database design. I am definitely not comparing myself with him nor with anyone but it surprises me sometimes when a technical (especially application dev leads) go ahead and design tables. In my thoughts I definitely don’t have any aversion towards the apps team or “great” tech leads, but in my shop its like “I create the mess! and then someone reviews and fixes.” Somehow, I don’t always appreciate the idea of  having app team design database until and unless this guy is definitely lot experienced in db design along with application development. At the EOD, its we who (DB guys) clean up the created mess. Oh, I am cribbing a lot again and always I express all these over my posts but my intent is to convey what is good, what is bad in DB process.

After a little dodging from this guy I decided to put in thoughts over what he had asked (Goal at the end of the activity). I am going to put what I had shared with him, and I would want you guys too to share your thoughts.

All these are from years of experience, so feel free to comment.

- Understand requirements through case studies, prototypes, documentation

- Map the requirements per module to a specific subject area (application modules, reporting, archiving, auditing, and any other few non-functional requirements)

- Identify key entities with high level attributes and prepare a conceptual data model

- Transform a conceptual data model to an E-R diagram with entities, attributes (key & non-key), relationships (1:N, N:1, M:N)

- Transform a logical data model into 3NF (3rd normalized form)

- Walkthrough the business analysts, technical team (application development & DB team), business team through the data model

- Identify the gaps during the walkthrough sessions and make changes to logical data model accordingly

- Transform an evolving logical data model to physical data model

- Review physical data model with Database Administrator to identify the type of objects that needs to be created in the DB instance (tables, triggers, data types, constraints [unique, foreign key, null able], indexes)

- Provide scripts to Database Administrator for creating tables and other objects appropriately in the table

- Provide guidelines in the volumes of data that could be generated for each of the table and plan for storage along with the DBA

- Ensure the scripts provided for object creations are appropriate per understanding with the app team & DB team

- Evolve data model as when requirements keep changing (new /adding)

- Ensure that the data model conforms to normalization process per normalization rules

- Ensure that the data model is flexible for changes in future and is not rigid which would cause overhead in future

- Ensure the process of synchronizing between the logical data model, the physical data model and the physical objects are smooth

- Provide guidelines to the team on the use of the entities, attributes, relationships from a logical data model perspective

Cheers!

SQL Server DBA?

I had an assignment around 2 weeks ago which was one of the toughest I could list out. One of the client had performance issue in one of the boxes and weren’t able to identify what could be the cause of the issue.

Someone from the DBA Group (where I don’t belong) had been identified for working on this – Identify the problem & provide recommendations. I was asked to guide this gentleman while he is on the job! Well, it just happens sometimes that I have to spend my energy thinking for my own activities on my table and also for others. And this is part of my job and I definitely won’t gripe about it. I told myself  “I need to help this guy just the way I am going to help me”.

All was set right to take off, but the only thing which missed from the beginning until the end of the exercise was that the DBA was not communicating. I did follow ups, mails, calls (short guidance’s), visited workplace  of  the DBA checked if we were in right directions and so on. All effort was on, and finally after the results were obtained from the monitoring scripts, the analysis phase started off.  The same communication problem was still existing during this stage too, and I was not knowing at all of what was happening at the other end.

One fine evening I get a recommendation list to be evaluated and validate after a single line follow up. The next day I spent 3 hours trying to collate information after large logs and trying validate. In vain!, I couldn’t complete and the presentation was scheduled the next day .  I gave up at the end giving my high level thoughts and as expected all these backfired where the client wasn’t happy with what was done.

And then one day I was asked to understand everything, analyze, recommend and present my findings. It was hell-of-days (5 days continuously) running through large logs (> 1.5 G), traces, performance counters and trying tie several pieces of information. A crazy time but I didn’t crib at all. At the end I could present findings and recommendations and I could save the face of the activity called as “DB Performance Analysis”. But one learning – Be whatever you are (multi-certified, well recognized), but things wouldn’t work if you don’t communicate. Communication is so important for any activity in life (work /personal). So guys if you such kind of situation I would say to help the other guy in communicating else you will be stuck as I was.

I hope this experience of mine helps anyone else out there.

Cheers!

Something is common

Hi Guys, I was reading through my favorite blog listing today, and I happened to read this blog entry by Linchi Shea. I totally agree with what Linchi says, and Linchi has brought out clear cut differences between a server and a desktop in a simpler way.

I would like to stress to the people who are in development projects that don’t host your databases on a desktop and say you have performance problems. And I hope the project managers, tech leads out there hear this. Please note that I am directing this message to people who really should know the difference.

Cheers!

At a Loss

This is how my life goes on too! An interesting article by Sean, and I am a regular reader of his frank blog entries on how a DBA’s life goes on.

http://dbarant.blogspot.com/2008/05/at-loss.html

Happy reading!