Solved

Database creation question

Posted on 2014-03-27
7
136 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 7

Assisted Solution

by:Steve
Steve earned 200 total points
ID: 39959134
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 37

Assisted Solution

by:PatHartman
PatHartman earned 200 total points
ID: 39959234
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
ID: 39959306
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 7

Assisted Solution

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

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 100 total points
ID: 39959398
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 37

Accepted Solution

by:
PatHartman earned 200 total points
ID: 39959421
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
ID: 40012940
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

739 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