jump to navigation

DB Designing? Objective /Goal October 30, 2009

Posted by msrviking in DBA Rant.
trackback

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!

Advertisements

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: