Solved

Database 'relationship designer'

Posted on 2014-07-26
6
248 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Why do we like using grid based layouts in website design? Let's look at the live examples of websites and compare them to grid based WordPress themes.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.

757 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

23 Experts available now in Live!

Get 1:1 Help Now