Database Havoc 3

[DataHugger]

I have been working for a small company for a year now.
The team we have is great. However, the databases we are using are starting to get out of hand. They have been crazy since the begining of the company because everyone on the data crew was fresh out of college.

It has gotten tot he point where I don't even feel like doing work anymore and just do engough to make things work.

We know where we want the databases to go, but I just get overwhelmed by the big picture.
My boss also does want to shell out for any software that could help us hygiene and dedup the data better. He keeps thinking that if we just keep putting more data on top it will just keep getting better.

Advice requested or commeseration:
And tips on how to hygiene/dedup data using only my skills and minimal software tools? and quickly?

Is it normal? How can I make my boss understand that it isn't "more data" that is the issue anymore it is making the data we have better.

I am just so absolutely frustrated by his expectations. I don't understand how he can expect us to magically fix everything in the database when he doesn't want us to get the systems to do it.

Thanks in advance for listening to me rant?

-DaTaHuGGeR

 

[DPlank]

Couple of threads in this forum that will interest you are:

The Platypus Database (thread655-606833)

and the Platypus Hunter's Revenge(thread655-846108)

from our good friend lespaul, who was in exactly this situation a while back...

If nothing else, these will show you you're not alone..


Keep the head above water..

Cheers,
Dave

Probably the only Test Analyst on Tek-Tips

animadverto vos in Abyssus!

Take a look at Forum1393 & sign up if you'd like

 

[lespaul]

who was in exactly this situation a while back...

and still in it!! We have been doing ERDs for some of the core systems (the charges, statutes, defendant & law enforcement personnell - which were the EASY ones!) but now we're stuck in the actual court processes (hearings, outcomes, sentencing and satisfaction of judgment). I have been in Analyst mode for the past few weeks sitting in the courtrooms trying to see what they do, what we will need to do to capture the relevant information and how to store it.

We're making progress though! I don't expect the database design portion to be done until maybe the end of this year.

As far as suggestions on what to do, I downloaded a trial software called DeZign for Databases (

http://www.datanamic.com)

and just started designing a normalized structure for some of the more basic areas of the court that I had been involved with. My boss found a draft copy I had printed and was impressed that I had taken it upon myself to start this project and subsequently assigned me to the entire database redesign! (If I had only known........)

You don't mention what database you are currently using, but one of the nice things about the DeZign for Databases is once you get the ERD completed, it will generate the SQL code needed to actually create the database you have designed.

Start with the tables and systems you are most familiar with, determine what you are actually collecting information about and then start defining your entities. Once you have the entities defined, decide which information "belongs" to which entity. You probably won't get it "right" first try, but you'll have a better idea of what you are working with.

Good luck!!

Les

 

[DataHugger]

You mean they actually give you time to design something?

I am jealous.

If it can't be done in a week my boss usually want to put it on the back burner.

So absolutely annoying.

I think I am going to try and figure out a way to write up a proposal and tell him my ideas in a whay that a sales guy can understand or I may just have to leave the company.

 

[lespaul]

They took away all my other projects and this is ALL i'm working on now!

Sometimes I would like something else to do, especially when I'm stuck on an idea or concept.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[DataHugger]

How did you get them to let you do that? What changed? How did they see the need?

 

[chiph]

I think the difference is that lespaul's boss doesn't have a short attention-span problem.

I don't think anyone can deliver anything meaningful in just a week.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[DoubleD]

I think I am going to try and figure out a way to write up a proposal and tell him my ideas in a whay that a sales guy can understand or I may just have to leave the company.

This is an excellent idea. Provide an actual proposal with specified software, as well as an estimated cost structure for the project. Now here's the key, include estimated Return On Investment (ROI). If you can show that your proposal will ultimately pay for itself, any sales guy will bite.
How do I estimate ROI?
1. How many hours are put in by you & your fellow associates to keep the old system up and running?
2. How many hours are spent on devising work-arounds to get the appropriate info?
3. What could the company do with the data that might help save money?
4. What benefit would other departments get from having clean, effectively stuctured data?

One way to structure the proposal would be:
Current State: How things are today?
Future State without changes: What will life look like five years down the road?
Proposal: What can we do different?
Proposed Future State: What will life be like with the suggested changes implemented?

I am what I am based on the decisions I have made.

DoubleD

 

[chris13]

hi,
today I'm not busy with the creation of databases (except for my own pleasure at home but was in former times and I have to deal with different DBs and their structure everyday. From what has been posted, some things come to my mind:

Normalisation is part of the design-phase of a db-schema. And the real work like writing all the queries and insert-statements follows. De-duplication is necessary when it's too late Probably not your fault...

Very often somebody does not tell you what he wants to be stored in a specific table, but what he wants to see when he draws his reports. That's a diference.

If you manage to define a layer of dictionaries (built of views, stored procedures, aliases, synonyms...) which is used by all the reports and 'userland'-applications, you get the chance to normalize the real tables and to keep up with their whishes, too.
Shure, this big step needs time to think. But most of the work can be done in the background.

When there is somebody who shows that he thinks about structure and design of an important part of the business -whithout beeing told so, but to improve his own work- the boss may be lucky. Cause it affects the future of the business. If somebody shows that he is able to do so, and to bring things forward, a good boss will give him the opportunity and assign the standard routines to somebody else. If there is somebody else... :-(
It depends on the boss and his way of thinking short-/long-term. One day I was lucky to have a boss who seemed to be dependant on his moods concerning daily communication and smalltalk (one might have thought "what an a**"), but he showed that he realized what we've been doing (overtime and creative solutions) and what it was good for. Some day he told me, that he doesn't mind seeing me 'relaxing' in the switchroom (reading pbx-docs), 'cause it would pay back sooner or later...

Yes, show him your ideas and how a salesguy will benefit. When it comes to the price (time), show that it's not a short-term project, but one that has to be done. Offer to spend xy hours per week or something like that.

Good luck,
Chris

 

[DataHugger]

Thanks for your suggestions though.
I am definately going to use if not offically at least for my peace of mind.

My boss is a short-term thinker.
I can tell by his now, now mentality.
Which is great for a new business and makes it pretty flexible, but if a thing that has a large cost now he
can't justify it.

The database was basically a large excel file before we got here. There were already dupes, and being fresh college grads and with alot of pressure on us we cobbled something together. We even told him from the begining that it was a horrible way to have a database set up.

I am so disgusted with myself that I am now doubting my abilities and spending an obscene amount of time complaining about my job on internet forums.

I hate slap patching things. It pisses me off.

 

[davidwendelken]

I'm assuming you know about primary keys, unique keys, and foreign keys and check constraints, and that your database supports them.

Let's assume you don't have any $ budget for tools.

Think about what data you would need to store to record the definition of each of those items. It's a very simple database. Knock out some dirt-simple screens for your own use to maintain them.

Now, make your best guess as to what the primary keys and unique keys for the tables "ought" to be. If you don't have a clue for a specific table, skip it! You're aiming for "much better", not "perfect"!

Populate the primary/unique key database with your educated guesses.

Write a small program to read thru your database and build select statements. The purpose of the select statement for each candidate constraint is to return the first count of the rows that violate the "constraint".

Write another small program that builds a select statement for a selected constraint that shows the relevant columns in the records. By relevant, I mean the key columns and any identifying columns that would help you decide whether this is a duplicate record that violates the constraint, or valid data that proves the constraint should not exist as proposed.

With these, you can quickly determine whether you can implement the candidate primary/unique key constraints relatively safely. You can even write a small program to build the sql to do it.

Repeat for foreign keys and check constraints.

Use the power of the computer to do the bulk of your fact checking. Think of this as writing reports for yourself!



David Wendelken

http://www.casetech.net