Database Questions

[danb4]

As a highly skilled database designer (a temp employee with almost no experience in databases) I have been asked to evaluate a system that some guy who retired made for the company I am at.

Here is the dilemma, I know it's ****, the guy who wrote it knew nothing of how a relational database should be set up. His interface consists of a page of buttons that are set up with macros, some of which work. I could redesign the interface, add the ability to do searches and reports based on user inputs, but it wouldn't change the fact that the database itself is a mess. One of the first tables in his database that I looked at was 31 columns wide, and should have been split into 4 or 5 separate tables.

What I need to do is figure out how to pull the info from his database and put it into a better designed one. I'm reading stuff on SQL, and I'd like to get them off of Access, cause the volume of information stored is very significant.

Enough rambling, here is my question:

What resources on the web(preferably) or in books do you recommend for learning how to move data from one database to another in the most efficient fashion?

I'm new to all of this, so resources on database design and implementation in general would be helpful.
I know this is a huge task, but I'm trying to at least understand their needs, even if I am not personally able to do all the programming. If they like what I come up with however, I'll probably just dive into it and try to learn what I need to know. Hey, I might as well be getting paid to learn this stuff.........lol.

[qball]

Well it seems your preliminary table analysis shows you're learning.

There are a ton of resources out there, but learning SQL is the right way to go. All relational DBs use it in some form or another.

Anyway, the most efficient way to move data between 2 dissimilar DBs is to understand the tables and data in both DB. Then determine the best way to import data into your new DB. Can it import tab/comma delimited text files? At worst, you need to create an INSERT statement for each row to be added to each table in the new DB.

The key here is understanding the table and data structure of the new DB. You need to match columns and datatypes for every row you will be importing!!!!

Then at worst, you export all of the data out of the old db (one table at a time) into text files. Open the text files in a spreadsheet. Move your columns around from the old table format into the new table's format. You may need to populate new columns with data that did not exist in the old DB and you may need to create new files for new tables that didn't exist in the old DB. Then save each spreadsheet (which corresponds to a new table as text) and use this output for a text import.

Iffin you need INSERT statements. Use a formula in the spreadsheet to create the INSERT from the data columns. Then cut and paste to get values and not formula. Then you only need to save this column as a text file and it will have all of the INSERT statements you need.

A lot to this, huh? We haven't even started on the constraints. The easiest thing it to leave these off of new DB until all the data is entered and add them later, manipulating data that causes errors.

There do exist data migration tools to do this stuff. But for the example you give, the manual approach will work best.

Hope this gets you started.

[danb4]

I need a good strong drink.

Thanks for the input qball, I think you've convinced me to go into management. I don't think I can actually do this stuff, so I should probably supervise those who do.

To do list:
Learn SQL
Learn how to work with an Oracle DBMS
Learn how to work with Unix
Learn Visual Basic
Have a good strong drink


I think I better start at the bottom and work my way up.

I wonder if Taco Bell is hiring.

Wonder how much unemployment is in this state.

****Wimper, Wimper********

Oh the perils of knowing just enough to get in wayy over your head..........

[JeffD78]

Tell me about it. I know enough MS Access to make a pretty decent database, but I have no knowledge of VB. Without that, nothing I do looks particularly good. I want to get out of the software thing altogether and I'm currently studying for the A+, and then the MCSE. I HATE ACCESS!!!!

[U-96]

There is a simpler way....

Print out your tables, with any links to other tables/records

Design and create your tables and relationships in Access.

Make a few rough data entry forms.

Hire dozens of data entry slaves to put the data in how you want it.

More realistically, as qball says, exporting into a spreadsheet, tidying up stuff and reimporting into something like Access is about the easiest, cheapest and quickest way. You MUST be sure that your inter-table links are preserved though. I really can't emphasize that enough.

There is also time to consider - if your database is mission critical, there will inevitably be some kind of down-time. There will be a point where data stops going onto the old system because you are sorting it out, but cannot go onto the new system because the old data is still being fixed for import. Catch22. If your company has a shut-down, that's a good time to do it.

If you are keen to fix your company database, this is about the best opportunity you will ever get to put it right - all those annoying little quirks in the old one, the stupid mistakes in the data etc...

Always design around what you want to get out of a database, not what you want to put into it http://www.sysopt.com/forum/smile.gif

And remember the three rules of database management:

back up data
back up data
back up data


U-96


JeffD87 - If you don't have it already, I thoroughly recommend Microsoft Access 97 Visual Basic (Step by Step) by Evan Callahan ISBN: 1572313196
(there may be a 2000 version, not sure). It was pointed out to me by socalgal some months ago, and it provides a really good primer in VBA and some of the deeper aspects of Access. It certainly helped me make a db the way I wanted, especially regarding the UI and linking to other apps like Word and Outlook. Good luck!


[This message has been edited by U-96 (edited 04-12-2000).]

[danb4]

Well, I don't think I made myself too clear on my plans. The current (but unused cause the guy who made it retired) Database is in Access, but I'd like to put something together that is either in the main Oracle database, or closely tied to it. It seems like a poor design for this department to be importing all it's info from the main database to a virtual duplicate, instead of working off of the main one. Currently, they are currently doing most their work manually(phone, fax, copy machine, sneakernet), and some tasks are simply not being done at all. So as for this department, there isn't alot in the way of existing systems to get in the way.

What they need is not an underpaid temp, but a consultant who knows the solutions well. I'd like to steer them in that direction, but carve a niche for myself in the process, maybe working with the folks designing the new system, maybe I could get a permanent position administering it and training folks on using it.

I am being asked to come up with the best solution for solving their tech needs, and I do not really know their existing systems, nor to I fully understand their needs. I guess the only positive is that no one is micromanaging me........but yeash....I think that might be easier than the current situation.


If I was just dealing with Access, this would be a lot easier. Doggone it! I wish I hadn't talked to my friend who is a consultant at Sun Microsystems, she rolled her eyes at the thought of using Access at all.

Simple Question: What are the limitations of Access? What kind of volume of information and traffic can it handle? Any examples you guys can give me of how much it can and can't handle?

By the way, I haven't found time to start on that to do list, but after today I may start working really hard on the last item on the list.

[qball]

Now my eyes are rolling. If there exists an Oracle DB, it sounds like this effort should reside there. I don't know all the reqs, so I can merely speculate.

As far as limitations of Access, I worked for 2 weeks on an Access DB/Application for a large non-tech firm. They used Access because it came with MSOffice and thus was free. It was designed by some IBM mainframers, and left much to be desired. Anyway, they had some tables with over 250,000 rows and were proud of it, fgs. We (myself and a fellow consultant) prayed the DB wouldn't crash under the strain whilst we were working there, as we would of taken the blame.

Moral:
If you are doing mission sensitive/critical processing, then go with a REAL DB. If you are dealing with a small office/department project, Access can be a great solution.
Real DBs offer much more complex operations, security, triggers, stored procedures, packages, functions, etc. That as things get more complex, allow for much more capable functionality.

Simple review of the migration algorythm I spoke of;

OLD TABLE NEW TABLE
person_id integer person_id integer
age integer name string
name string age integer

Export old table comma delimited, like so;
1,28,Bob
2,42,Carol
3,56,Tom

To import into new table, we need to move the columns around so the data and datatype match the new table columns, like so;
1,Bob,28
2,Carol,42
3,Tom,56

This new file can be imported into the new table successfully.