Solved

Database 'relationship designer'

Posted on 2014-07-26
6
253 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 45

Expert Comment

by:Kdo
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 45

Expert Comment

by:Kdo
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 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 31

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 108

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
.php tree directory? 5 55
Does video get better conversion rates? 2 28
Update from TABLE-A to TABLE-B 5 38
append to an ms access field 6 21
Envision that you are chipping away at another e-business site with a team of pundit developers and designers. Everything seems, by all accounts, to be going easily.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmaker…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now