Building a new database built on a spreadsheet design structure... help appreciated. (1 Viewer)

[jworden1]

Good evening,

I was hoping that someone could help me with regards to a database design. I run a market research company and currently use very large spreadsheets to store my data. I use one workbook per client, made up of several worksheets.

It has been put to me though that what I am actually creating is a database and that rather than using Excel I would have a greater benefit using Access, particularly when it comes to creating reports.

Below is a list of the different worksheets I use per workbook. I wonder if someone would be kind enough to help me build a relational database to store this information.

WORKSHEET1: EXISTING CUSTOMERS
Name
Contact Details
Location
Company Profile
Description of Sales in Progress
Sales Value
Status of Sales
Interview Data

WORKSHEET2: HISTORIC CUSTOMERS
Name
Contact Details
Company Profile

WORKSHEET3: POTENTIAL CUSTOMERS
Name
Contact Details
Company Profile
Interview Data

WORKSHEET4: MANUFACTURERS
Name
Contact Details
Company Profile
Interview Data

WORKSHEET5: INTERMEDARIES
Name
Contact Details
Company Profile
Interview Data

WORKSHEET6: COMPETITORS
Name
Contact Details
Company Profile
Interview Data

As you can probably appreciate there is a lot of information held within each of the spreadsheets, a lot of the worksheets hold similar type data and by opening up a workbook on a particular client I can look at their customers, competitors, intermediaries, etc.

As a fairly novice user can someone direct me to how I could create a database that holds data about all my clients, so that I may open my database, choose a client and then view their customers, competitors, intermediaries, etc. So in essence I can do the same as I do now, it’s just that all the information will be in the same place.

If anyone could set me off, perhaps with the aid of a simple diagram I would be most appreciative. Note that I do not want to move the actual data from Excel to Access, I just want to start afresh so in truth I'll only be moving the structure (limited as it is) across. I don't know if this is relevant but thought it was worth mentioning.

Kind regards

 

[kcolbert01]

Depends...

your approach to the database really depends on what you will use it for. will there be forms or reports or will data be auto updated? all these (and possibly much more) are factors that you need to consider.

as for now - your worksheets with the customers basic info can be put into one table with one field that says what type of customer they are (historic, potential, etc). if you assign customer numbers then you can create other tables that might hold particular information to that customer type and create the relationship based on the customer number. that way if a potential customer becomes an actual customer, you can retain all of the data from before.

i hope this makes sense. it sounds like access would be a better tool for you.

 

[jworden1]

After playing around with Access for a bit...

Thank you for the response so far... it is much appreciated. I've had a bit of a play around with Access so far and this is what I have come up with.

I think I'm only requiring a simple relationship diagram, or at least that's how I'm approaching this.

You may be able to see the work I've done so far through the relationship diagram shown below, if not it can be accessed here

I know the relationships and the design aren't perfect, I've been told that some data redundancy(?) could exist but I am happy to proceed at this moment in time with the current design and alter it later. Basically I just want to see for myself the benefits of Access.

Could someone please explain to me how I may have a competitor existing for two of my clients, similarly some of my clients have the same existing company as one of their competitors. The design I am currently using only allows each competitor to be linked to one client, although a client can have many competitors. Can someone point towards a solution to this problem?

Kind Regards.

 

[The_Doc_Man]

Before you go too deeply, I have some important advice for you. Search the WEB (not only Access) for "Normalization" and read a few good articles. I suggest that you concentrate on .EDU sites from universities you recognize.

After that, you need to search some of the threads where I responded to other requests for design help within this topic. I have often posted notes on how to approach the process of entity discovery and initial design. RIGHT NOW is the most sensitive time of your implementation. Nicklaus Wirth, the "father" of the Pascal programming language, went on record as saying that 80% of all data processing problems in programs are due to poor data design. I think his estimate was low, but what do I know?

If you get the design refined closely enough, you will find that it supports a lot of things. If you get the design wrong, you will spend the life of that database fighting it rather than enjoying it. So take time NOW to analyze the problem in order to get the design right.

 

[BrettStah]

What he said.

 

[dsigner]

The major consideration here is that the existing spreadsheet is your business.
If you create a database which is poorly designed then it is going to limit your business. It is easy to think that you will just start somewhere and redesign it later. The truth is that it will be just too much work so that it will probably never happen. I have worked for several companies which were crippled by their computer system because they could never face the disruption needed to change the design. Actually what happens is that it gets patched and fixed again and again until nobody even understands what the design is. This creates a situation where you can't change it in case it no longer works at all.

I would suggest that you need somone else to work with at the design stage because a fresh mind can ask the questions which you are too involved to even consider. This can be someone who is on attachment to your business
and they don't need to know Access. They do need some database experience and preferable some market research experience.

Once the two of you have got a solid design you can implement the Access database learning what you need as you go. The will ensure that you have the detailed knowledge which you might not wish to share with someone who may leave the business.

Hope this helps

 

[CraigDolphin]

Normalization is vital to your success with a relational database. Later is too late to set up your table relationships correctly.

For example, what will you do if a company/client has more than one contact person? More than one address? (mailing vs physical)

What if a competitor is also a (sometimes) customer?

What is the difference between a 'client' and a 'customer'?

Also, you should look into naming conventions for your fields...avoid spaces and any other reserved characters as they can foul you up in SQL/VBA.

Instead of defining tables by whether the records are historical/potential/current customers try to create tables based on real-world entities.

Consider something like this simple model...

Orders---Products
|
Companies --- ContactPersonnel
|
Addresses (Type, Street, City etc)

This way you can define a company as historical/current/potential using order information (date for example) so that companies who have ordered something in the past 12 months are 'current', who last ordered something more than 12 months ago as 'historical', and who have never ordered something as 'potential'.

A field in the company table can record whether a company is a competitor, a customer, or both.

Depending on your needs, you may want to include a order-price history table that saves prices for orders in case you need to track actual order totals over time (prices of products may change over time and if you rely on a price field in your products table to generate this information later then you have no way to correct for this)

 

[The_Doc_Man]

It would appear that those of us who posted all agree, step back and look HARD at your business - NOT your design.

dsigner said this:

the existing spreadsheet is your business.

While in physical mapping such as geographic maps, the map is not the territory, very often in business the territory BECOMES the map. That is, your model suddenly becomes the thing that runs your business. You really want YOU to run the business and the model merely tracks it.

What you want, then, is a damned near perfect map/model. Because if you try to run your business from the model you are about to build, you can only do things in your business that the model condones. And therein lies the problem. Once you start running your business off of whatever you built, the tail wags the dog. It is supremely important to get it right and let the dog wag the tail.