Database 'relationship designer'

I think we missed the boat in designing our database, and our needs have morphed into something different.  My guess is that this happens allot between those with the business need and the developers, and has thus prompted this question.

Let's say we want to do our project in MySQL but don't want to dive right into designing the tables until we have the relationships, requirements, and so forth drawn out first.

What is the best way to do this for step one of creating a database for any purpose.
frugalmuleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenData Warehouse Architect / DBACommented:
Hi Mule,

A few background questions, first.  :)

- Do you have a database that you're currently using?  Does it do what was intended?
- Can you accept having two databases?  One for the business purpose, and another one for reporting and analysis?
- Do you know what the data is and how it will be needed?


Kent
0
frugalmuleAuthor Commented:
Good questions.  

Yes we have a database we are currently using, but since my developer hasn't figured it out yet, we don't have a front-end and I have no way of knowing whether it does what is intended or not.

Yes, I can accept having two databases if the reason for them is clear and not a decision my developer made on his own.

Yes, I do know how it will be needed.  That's the trouble part because I have tried to write it up in multiple ways using documents, task lists, spreadsheets and so forth.

What I need now is some efficient way to represent that so that we can come to a collaborative agreement on how to proceed.
0
Kent OlsenData Warehouse Architect / DBACommented:
Good answers.  :)  It makes things a lot easier!

There's really two big directions that you can go.  Re-engineer the current database to meet the newly defined needs, or engineer a reporting database that is built from the current database.

And of course, there are advantages and disadvantages to both ideas.

Re-engineering the existing database is the riskier solution.  
  - It will require a conversion from the current structure to the new one, and will probably mean application changes to deal with the new database.  
  - If the application is accessing the database from a set of well defined APIs, the risk gets a lot smaller as the changes to the application become smaller, too.  
  - If the APIs can accommodate the new design, very little has to change with the application.  
  - If the database is evolving with the business, you may find the need to make database changes, of varying complexity, several times.

Building a reporting database may be the more complex solution.
  - The database could be built with zero downtime or changes to the application.
  - It would be similar to the current database, except that it's structure should be optimized for reporting.
  - Updating the reporting database may be challenging as it may be difficult to capture changes in the current database.
  - How much of a delay can you live with in getting data from the production database to the reporting database?  Real-time?  Hourly?  Daily, etc.
  - You would then have two databases to maintain.

Do you have a DBA?
Who is going to architect the changes into the current database?  Into a reporting database?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Ray PaseurCommented:
Worth learning about these.  They create a good thought framework for describing the data and relationships.
http://en.wikipedia.org/wiki/UML_Diagram
http://en.wikipedia.org/wiki/ER_diagram
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Paul SauvéRetiredCommented:
You might also want to consider Database normalization.

From Wikepedia:
Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy. Normalization usually involves dividing large tables into smaller (and less redundant) tables and defining relationships between them. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database using the defined relationships.
You can find a brief tutorial here: phlonx.com
Introduction
This is meant to be a brief tutorial aimed at beginners who want to get a conceptual grasp on the database normalization process. I find it difficult to visualize these concepts using words alone, so I shall rely as much as possible upon pictures and diagrams.
0
Ray PaseurCommented:
@paulsauve: Good idea!

It's also very interesting to make a Google search for the exact phrase, "Should I Normalize My Database" and read the very interesting discussion on different sides of the question.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.