Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Process of building a database (platform independant)

Status
Not open for further replies.

03Explorer

Technical User
Sep 13, 2005
304
US
I am in the process of building a database schema/model for data that is currently captured in numerous formats and styles (excel single record per xls, excel many records per xls, old access db's (poorly designed), etc) I am identifying the fields and constructing a normalized table structure. Is there any web site that helps with the project steps? A few feet out view of the project?
ie
step 1 - Gather all documents to be building a database from
step 2 - obtain a sample of data (i choose 5 rows) to begin normalizing
step 3 - draw a diagram with relationships to tables based on step 2 and fields used. Incorporate foreign key fields to link the tables (this done using Visio)
step 4 - ??
step 5 - ??

I am looking for some kind of guidance on a proper method to begin designing a database (independant of application). Something I can hand to a DBA and most of the project is ready for the action of building.

Is there a web site or book that can help?

Thanks
 
Or am I wrong by thinking that I should diagram it on paper/Visio first, by displaying what I believe the tables should be and their relationships?
 
I would diagram it (although I must admit, I usually do this step in my head) before attempting to normalize any of the data. This ensures you have a properly designed database, not a kludge-tastic monstrosity whose design was influenced in any way by the horrible state that the data is currently in.

If you need a refresher on normalization here is a good one:


Just my .02 (and its' probably worth less than that ;-) )

Alex

Ignorance of certain subjects is a great part of wisdom
 
From what I surmise to be 3,500 miles away, I suggest the following:-

Since this is 'Heinz 57 varieties' of software, check to see if the business requirements for some of the legacy stuff is still there. If it isn't, don't even bother to migrate the dross.

It's better to go to your boss and explain that by spending time on checking requirements, you've either deleted unnecessary tasks, or confirmed the ongoing business requirement, which formally justifies the expenditure on your work.

Second, zoom out a bit, and write (on a piece of paper with a pencil) a business and/or data flow diagram, rough sketch only. Since this is the first time that all these things have been brought under one roof, this might well reveal duplicated and/or similar business and data paths, as well as highlight redundant processing steps, which only exist for historical reasons, and are no longer needed (even if the overall business requirement persists). Does the accounts department still need to know all about a distant managers expenses, just because 3 years ago he used to work in accounts?

Also, if you start off by bashing out tables and normalising, you might end up normalising stuff that you simply don't need. Although normalisation is always to be commended, normalising useless data is 100% wasted effort.

I think I'd summarise this by saying "take a reality and requirements check on the whole shebang", before going anywhere near a keyboard.

T

Grinding away at things Oracular
 
I know this is a project that is needed to be done. The boss has granted time to persue this project.

My question is simple... is there any kind of process in order to complete or work on this project, like steps? Either by book (again platform independant) or web site?

I am at the normalizing step, but what is preferred to be next and then after that step until a programmer needs to be involved (when a platform is decided on).

Does that help?

As for the data... we are working at standardizing the data being collected and not migrating old data. Only new data entry would be used from the point of product completion.
 
You'll probably want to look at considerably more than 5 records or you will miss data exceptions.

I would do the steps you defined and then create the following (not all inclusive but it's a place to start)
Diagrams of the table structure including data types
A relationship diagram of each of the current ways the diagram is stored that points it to where it will be sotred in the redesign.
(this is a must for this project) _ set up a development environment. You will probably need to do several round s of testing to manage to get the data moved correctly, so you want this to be in a separate environmant. Make sure to script your steps so that you can do them easily when it is time to load the final data.

Look at how data is to be entered - describe what business rules the user interface will follow. Rough out what the user interface will look like and show it to the users for comment. You will find that they will almost never find the way you want to set up the forms to be the way they want to enter the data. If they are entering from paper data, try to put the columns in the order that the data is on the paper wherever possible - it makes data entry faster and will reduce errrors.
I'm sure there's more but I must get back to work.
Look at how the data is to be reported out. Create a list of reports and define what columns will be in each. Comsult frequently with the users on this step and the previous one.

Questions about posting. See faq183-874
 
I found a product called 'Database Architect'. It is a shareware application that is simple enough to put tables together and helps with relationship diagramming. It seems limited beyond that for what I want it to do, but for now it is my pencil to paper method.

URL is: Database Architect

Let me know if anything else will work.

I am looking into purchasing the books listed above... Thank you for sharing elsenorjose :)

 
I found a product called 'Database Architect'. It is a shareware application that is simple enough to put tables together and helps with relationship diagramming. It seems limited beyond that for what I want it to do, but for now it is my pencil to paper method.

URL is:
Let me know if anything else will work.

I am looking into purchasing the books listed above... Thank you for sharing elsenorjose :)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top