Archive for Database Modeling

| December 2, 2013
4 comments

SQL Advent Calendar 2013 – Day 2 – Macro to Remove Common Fields in ER Studio Data Architect

First Post in this series: SQL Advent Calendar 2013-Day 1- Placeholders in SQL Prompt Snippets

AdventCalendar02As I was hanging our family Advent Calendar up, I thought I would make one for you, my readers. I’ll be sharing my scripts from two of my favorite products as well as tips on how to create your own.

Behind Door Number Two

ER Studio Data Architect by Embarcadero (ER Studio DA) has the ability to create macros, which makes me absolutely happy. Why? Because I don’t like repetitive work. If I can create a macro, then I can have the computer do the repetitive work while I go get a peppermint mocha.

One day I was working on a macro that created fields for multiple tables. I realized during the development process that I also needed a macro to delete fields for multiple tables. So I created the macro below. It has come in handy many times.

'----------------------------------------------------------------------------
' PURPOSE:  	This macro adds base attributes to entities that are selected.
' Author:   	Mickey Stuewe
' Date:     	9/3/2013
' version:      1.0
'----------------------------------------------------------------------------

Sub Main

Dim objModel As Model
Dim objSelObject As SelectedObject
Dim objEntity As Entity
Dim iCounter As Integer

Begin Dialog UserDialog 440,98,"Continue?" ' %GRID:10,7,1,1
	Text 20,21,250,28,"Are you sure you want to remove a field from the selected tables?",.lblOwner
	OKButton 300,21,120,28
	CancelButton 300,56,120,28
	Text 20,56,80,14,"Field Name:",.Text1
	TextBox 100,56,170,14,.txtFieldName
End Dialog

Dim dlg As UserDialog

If Dialog(dlg) = -1 Then

	Set objModel = DiagramManager.ActiveDiagram.ActiveModel

	' Iterate through all the selected objects in the current
	' model.
	For Each objSelObject In objModel.ActiveSubModel.SelectedObjects

		'Get the object type - we are only concerned
		'with entities.
		If objSelObject.Type = 1 Then

			Set objEntity =  objModel.Entities.Item(objSelObject.ID)

			objEntity.Attributes.Remove(dlg.txtFieldName)

			iCounter = iCounter + 1
		End If
	Next objSelObject

	MsgBox dlg.txtFieldName & " was removed from " & iCounter & " Table(s).", vbOkOnly + vbInformation, "Attributes Removed From Tables"
End If

End Sub

To use this macro, follow the steps below.

  1. Add the macro to the library. (Tomorrow I’ll be expanding on this step.)
  2. Add the macro to the shortcut menu for Tables.
  3. Select several tables that have a common field you would like to remove.
  4. Implement your new macro
  5. Watch the unwanted fields go away. (When I do this to 30 tables at one time, I actually feel giddy. True story.)
Note: You can download this macro from my Script Library under Resources. It’s called Remove Attribute From Selected Tables.

Next Post In Series: SQL Advent Calendar 2013 – Day 3 – Organize Before All The New Toys Come In

Category: Data Modeling, Database Modeling, ER/Studio Data Architect, Script, SQL Advent Calendar, T-SQL | Tags: , , , ,
| November 15, 2013
2 comments

SQL Tidbits: No.1 – Outputting from ER/Studio Data Architect Directly to SQL Server Management Studio

I’ve been tossing around some ideas for my blog. One of which is to provide a quick tech tip… or a SQL Tidbit. The idea is to keep me writing until I get used to blogging EVERY week. Hopefully in a few months I’ll have more SQL Tidbits than there are Grape Leaves in this picture.

So let’s get started with the first SQL Tidbit.

In the beginning…

I’ve been using ER/Studio Data Architect for a few years now. If you’re not familiar with it, it’s used for modeling and maintaining database schemas. I love this product, but I’m not a big fan of the default application (Universal ISQL) that the change script is sent to. Mostly because Ctrl+A doesn’t work in the query window and I find it clunky.

At my previous company, one of my coworkers created an application that ran in the background. When it detected that a change script was ready to be deployed, it would capture it and open it in SSMS.

But there is an easier way

When I started at my current job, I was setting all the defaults that I like in ER/Studio Data Architect and I stumbled upon this setting that I didn’t know existed. This setting lets YOU pick the editor for the change scripts to be deployed to. I changed the path to SSMS and I didn’t have to see the default application any more. (WIN!)

To get to the setting, do the following.

1. Click on the Tools/Options menu item.

2. Click on the Tools tab on the right hand side of the dialog box.

3. Change the ISQL Path setting.

 

And that’s a wrap

In the next SQL Tidbit I’ll share a script for cleaning up temporary SQL objects.

Category: Database Modeling, ER/Studio Data Architect, SQL Tidbits, T-SQL, Tools | Tags: , , ,
| July 9, 2013
One comment

T-SQL Tuesday #44 – Whoa. Déjà Vu…It Happens When They Change Something

 

Bradley Balls  (b|t) is hosting this month’s T-SQL Tuesday blog party. The party was started by Adam Machanic (b|t) in December of 2009.  The topic this month is Second Chances. Bradley has asked us to write about something we would like to have changed if we were given a second chance. I’m going to write about something that I actually was given a second chance to do differently.
Setting the stage

[Neo sees a black cat walk by them, and then a similar black cat walk by them just like the first one]
Neo: Whoa. Déjà vu.
[Everyone freezes right in their tracks]
Trinity: What did you just say?
Neo: Nothing. Just had a little déjà vu.
Trinity: What did you see?
Cypher: What happened?
Neo: A black cat went past us, and then another that looked just like it.
Trinity: How much like it? Was it the same cat?
Neo: It might have been. I’m not sure.
Morpheus: Switch! Apoc!
Neo: What is it?
Trinity: A déjà vu is usually a glitch in the Matrix. It happens when they change something.

The Matrix (1999)

First past

This is an ironic topic. Today is the last day at my current job and the topic I’ve chosen to write about has to do with one my first assignments four and half years ago. I first arrived at my company in January. Do you know what happens in January? The Sales Team has a new structure that needs to be applied to all of their reports as of yesterday. That particular year, they added a new layer to their hierarchy. The database model couldn’t handle it and neither could the reports. I proposed a new model using recursion, both in the database model and in the reports and it was approved. It proved to provide flexibility in the years to come. It had one flaw remaining though. It had maintained the current practice of assigning Clients to Sales People. That doesn’t sound too bad, until you know that when a Sales Person leaves, all of their Client records have to be updated… one by one by someone in sales. It also caused problems when there wasn’t a Sales Person available to assign to the clients right away.

Changing direction

This past January I had an opportunity to improve upon my original design. I simply changed directions. In the past, each Client had a Sales Person and each Sales Person had a Territory. Now, each Client has a Territory and each Territory has a Sales Person. If someone leaves, only ONE Territory record needs to be updated with a new Sales Person. If a new Sales Person is not available, then the Territory still shows up in the reports. This change was completely transparent to the report users.

I created a sample database model to show the relationships.

TSQLTuesday44 - DB Model

Data Model Created in Erwin

The cherry on top

The best part came a month after the new model was implemented. The Sales Team needed to have a single Sales Person represent different Territories in different Parent Territories. That was not possible with the old model. A Sales Person could only have one Territory, but with the new model it was possible… and it was already in place.

Thanks for all the fish

Thanks go out to Bradley Balls for hosting this months T-SQL Tuesday blog party. Please visit his website at http://www.sqlballs.com.

Category: Community, Database Modeling, T-SQL Tuesday | Tags: , ,
  Recent Entries »
%d bloggers like this: