Solved

Database creation question

Posted on 2014-03-27
7
132 Views
Last Modified: 2014-04-21
I know this is elementary for many of you, but not for me at this point in my SQL learning curve.  I am starting to design my first database and have a question about the best way to do the following:

I intend to create several tables - each having to do with customer problems.
One for customer information,
one for type of problem (to function as a list of areas where the problem occurred in production)
one that has the following - problem number (as a key) customer, type of problem, description of problem, resolution of problem.

This will eventually plug into something much larger, but for now this is the starting point.
I plan to use Access as a front end for this and SQL server 2008 as the database.
I would like to do the following:
1) have some of the customer info (email, city telephone, etc) auto populate when the customer is selected if they are already in the customer table.
2) I would like to be able to have a list to view these problems by customer, problem type, and eventually one or two other parameters that I haven't worked on yet.

So, my question is this:
What is the best way to setup these databases so that I can more easily do the above?  I want to avoid a lot of duplication of data and would like for it to be fairly easy for a person who knows practically nothing about sql to sort through some of the data.
0
Comment
Question by:mjburgard
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 7

Assisted Solution

by:Steve
Steve earned 200 total points
Comment Utility
I suggest looking through the MS Acces example database. The Northwinds DBs have many examples of what you're looking for and were built (in most Part) to best practice standards.

You may find you only need to make a few changes to the layout that they have already. Even in SQL, the basics of how to split the data amongst tables may be very close to what you need.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 200 total points
Comment Utility
I disagree with the templates being best practice although some are better than others.  There is at least one sample of problem reporting that would give you a start.  Once you have modified the tables to suit your needs, then you can use the upsizing wizard to transfer the table to SQL Server.  But if you are not familiar with SSMS (SQL Server Management Studio), start with Access/ACE.
0
 
LVL 1

Author Comment

by:mjburgard
Comment Utility
I have been using ssms for quite awhile -  I just have never designed my own database - I have found the Template that looks like it was made for an IT department to register computer issues - would this be the problem database you were referring too?
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 7

Assisted Solution

by:Steve
Steve earned 200 total points
Comment Utility
Problem "resoluton" database I think is what Pat meant, and yes, that is a good place to start.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 100 total points
Comment Utility
There's really no substitute for going through the logical design process.  You'll need someone that knows how to do that, i.e., with experience.  You could try just Google-ing the steps and following them yourself, but it's not an intuitive process.

Do not mix or combine the logical and physical design phases, they must be kept separate.  Once you have a good logical design, converting it to a physical design is very easy.
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 200 total points
Comment Utility
I should warn you to be careful with the tables in the templates.  Some of them contain data types that cannot be upsized to SQL Server.  I needed a customer table in a hurry once and I started with the built in customer table.  That was a mistake.  Even though I deleted the abomination data fields, Access remembered they were there and never let me save the data base as A2003.  You will have the same issue in trying to upsize them.
0
 
LVL 1

Author Closing Comment

by:mjburgard
Comment Utility
All of these helped - I have designed the database (I had already done the logical design).  The warnings were sufficient to make me go straight to SQL express and skip Access as a solution.  Instead, we are using Access and Excel as the front end depending on what type of data we are accessing and adding.  Thanks for all the help.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

772 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

11 Experts available now in Live!

Get 1:1 Help Now