Solved

Database creation question

Posted on 2014-03-27
7
133 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
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 34

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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:ScottPletcher
ScottPletcher 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 34

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

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.

Question has a verified solution.

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

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

912 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

20 Experts available now in Live!

Get 1:1 Help Now