Advice on Normalising Process (1 Viewer)

[PaddyIrishMan]

Hi all,
I’m looking for some opinions on the following scenario please:

Currently my company uses lots of different databases:
A Database for tracking support calls A Database for tracking Software Errors
A Database for storing Employee TimeSheets

(It goes on…)

I want to normalise this whole process & be able to use the data to generate metrics for the whole process.

My Question(s):

Should I create one BIG Database with lots of forms & a switchboard, this one database could be used to record everything?

Should I keep the databases separate & link the tables that share information?

If I am to link the tables, should I link to ‘Live’ tables or rather than link tables just have a local copy of the table & update it every time the database is opened?

Any Comments or Suggestions graciously welcomed.

Thanks in advance,
Patrick.

 

[The_Doc_Man]

To answer that question, I think we would need to know more things than you told us.

Questions that should be answered (and the implications thereof) are:

1. Is there a NEED to consolidate databases? For instance, you mentioned the issues of data sharing among separate DBs. Is that reaching or at least heading towards a problem situation? Implication: If it ain't broke, don't fix it.

2. Are the DBs all implemented in the same general development environment? (If you use both Access and ORACLE TOOLS, the answer would be no.) Playing like a kitchen blender is much harder when you have more than one ingredient in the mix.

3. If the answer to 2 is NO, what kind of learning curve will your management team accept for the folks who would have to change their way of doing business? And what amount of business disruption can be tolerated in adapting shop procedures to the new system? Your boss might have to fight you on this one if it costs too much in collateral arenas.

4. What political climate exists in your shop regarding data ownership? I.e. would this consolidation step on someone's toes enough to make a really big enemy? Some shops tend to be highly posessive about their data sets. The shop managers might fight you because they might see it as a loss of personal standing or power or influence.

I'm not going to ask mundane questions like, can you even fit all of the records into one database. You would find that out soon enough anyway.

I'm not going to ask silly questions like, can you afford the PCs and MS Office licenses and such. You obviously think you can.

As to the link to live vs. link to copy question... If you went that way, I would say that you need to know the updating cycle of the holder of the linked table before you would know whether it made any difference at all.

 

[PaddyIrishMan]

Thanks Doc_Man,

In Response to your questions:

Q1 - I believe there is a need to consolidate the data, at the moment the individual databases don't follow any predefined format & have lot's of redundant information. The existing data would require major cleaning because there is no consistency - i.e. There might be a Product stored as: MicroSoft Office in one database & MS Office in another. Thus there is no easy way of getting the bigger picture when looking at the internal data metrics.

Q2 - All Databases are Access2k

Q3 - See Answer to Q2

Q4 - This is not an issue (I'm 6'2 & full of muscles ;-)

To give you a better idea of what I'm trying to do:
Up to recently, all internal databases have been created by normal users with little knowledge of MS Access. These databases are awful & need upgrading.
I like the idea of being able to use all of the internal data to our advantage -
i.e. I want to see how many bugs are in a product
I want to see how many support calls have there been for a project
I want to see how much development time is involved between change requests.
etc.
As you can understand, this is a huge request & it's gonna take a lot of work. I'm still in the planning stage & want to see what options I have.

Thanks again,
Patrick

 

[FoFa]

The_Doc_Man makes many valid points. However if you decide to move forward with this, consider the ramifications of your datawharehouse. Maintenance is the biggest headache you will encounter. Typically the performance is not as big an issue because of limited usage, rather than maint. Having a single source of data collection is much easier to maint. than many. So if you decide to move forward, you may want to drive your design by how maintanable is it. If everything was in Access DB's, you may be better off creating a database of linked tables to all your external DB's if there are no network issues. Here we use SqlServer, and we move data to a central point using a combination of MMS and DTS. 2 points for Maintenance, and that is becoming increasingly hard to monitor as data is added.
Just my 2 cents worth.

 

[PaddyIrishMan]

FoFa,
Thanks for your 2 Cents.
Maintenance is hugely important to me. Ideally I will develop this system then have somebody else administer it. This is a major factor in my planning efforts.
I'm thinking that I might create a database with no forms etc. just tables. This database could store all of the shared information in it's tables & all of the other databases could just plug into it via linked tables. The problem being that should the databases be on different machines, if the network crashes then all of the databases are useless. Maybe my idea about updating local tables from the central database every time the user logs in would solve this issue?

Regards,
Patrick

 

[The_Doc_Man]

Paddy, all of your points are valid. Just make sure that you get your management team on board. In particular, the one that really riles up folks is this one:

3. If the answer to 2 is NO, what kind of learning curve will your management team accept for the folks who would have to change their way of doing business? And what amount of business disruption can be tolerated in adapting shop procedures to the new system? Your boss might have to fight you on this one if it costs too much in collateral arenas.

Even if NOT changing from something else to Access, you are going to disrupt business flow during the transition period. So just be aware that there is an iceberg floating in those serene waters, just waiting for a titanic project to impede. And 90% of an iceberg is submerged, you know...

(Sorry, I'm in a giddy mood today!)

 

[PaddyIrishMan]

3. If the answer to 2 is NO, what kind of learning curve will your management team accept for the folks who would have to change their way of doing business? And what amount of business disruption can be tolerated in adapting shop procedures to the new system? Your boss might have to fight you on this one if it costs too much in collateral arenas.

Even if NOT changing from something else to Access, you are going to disrupt business flow during the transition period. So just be aware that there is an iceberg floating in those serene waters, just waiting for a titanic project to impede. And 90% of an iceberg is submerged, you know...

(Sorry, I'm in a giddy mood today!)

LOL

Sometimes you need a whack of an iceberg to let you know youre still alive!