Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Database 'relationship designer'

Posted on 2014-07-26
6
Medium Priority
?
281 Views
Last Modified: 2014-08-14
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.
0
Comment
Question by:frugalmule
6 Comments
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 40221632
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
 

Author Comment

by:frugalmule
ID: 40221643
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
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 40221657
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 40221729
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
 
LVL 33

Expert Comment

by:Paul Sauvé
ID: 40221833
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40221847
@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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Exchange database can often fail to mount thereby halting the work of all users connected to it. Finding out why database isn’t mounting is crucial and getting the server back online. Stellar Phoenix Mailbox Exchange Recovery is a champion product t…
How do you create a user-centered user experience on your website? And what are some things you should consider in the process?
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question