Database creation question

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.
LVL 1
mjburgardAsked:
Who is Participating?
 
PatHartmanCommented:
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
 
SteveCommented:
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
 
PatHartmanCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mjburgardAuthor Commented:
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
 
SteveCommented:
Problem "resoluton" database I think is what Pat meant, and yes, that is a good place to start.
0
 
Scott PletcherSenior DBACommented:
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
 
mjburgardAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.