?
Solved

Database creation question

Posted on 2014-03-27
7
Medium Priority
?
138 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 600 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 39

Assisted Solution

by:PatHartman
PatHartman earned 600 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 7

Assisted Solution

by:Steve
Steve earned 600 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 300 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 39

Accepted Solution

by:
PatHartman earned 600 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
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.

719 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