Customers from a same company different campus

[Alficeman]

I am trying to build a database to quote my products and i have a challenge managing customer information.

Let's say i have a customer working for a university, and this university has several campus around the state and different campus has different faculties. Each faculty has several departments. Each department may have several buildings and a building has several labs.

I want to have all this information when a customer places an order (so warehouse knows where they should ship the products), and in a perfect world i will have several customers from the same campus, faculty and department, but different labs. And more than one university will be buying my products.

How many tables do i need to create for this? A customer table with personal info on the customer (name, phone, email, position)? another table with Universities info? another table for campus details?

Any help is welcome.

[#2]

I don't think so. That's way too much detail for too little gain. I would start with talking to the people in the Shipping department. They have been getting packages to their destination for years. My guess is that you need to capture a few Shipping Address fields at the point of order entry, and that's all there is to it.

[#3]

@Alficeman 

Here's a slightly different take.

I see two related, but not identical uses for information in this system.

One is the shipping information needed to get products into the right hands.

The other is the marketing information which you might gather in order to target your marketing materials to different audiences. 

It seems tempting to want to merge the two, but it might well become too complicated in a single database. Maybe you could think in terms of a marketing application and an operations application.

You could import information from the operations application to support marketing decisions. Or you could implement a marketing tool like SalesForce (although that's possibly on the pricier end of the scale for your needs.)

[#4]

Thank you very much your answer, however i do not intend to use this database for marketing, i am just wondering how to enter the information on the lab next door, since i do not want to duplicate the fields for campus main address, faculty, buildings, and department. I know that the lab info, which is the last piece of information has to be unique.

[#5]

Tom, thank you for your answer, i do respect your point of view, however, the ability to pinpoint the exact location of the customers is very useful. Like what will happen if the sales man is out of town? So, there is a real need for that.

[#6]

> pinpoint the exact location of the customers is very useful
The point I was trying to make is that you should capture that when the order is being taken.
"Arizona State University"
"Department of Engineering, Earthquake Lab, Building 4, Door 3"
"123 University Drive"
"Tempe, AZ 85000-1234"

How was your chat with the Shipping Department?

[#7]

Hi Tom,
The chat it was not very useful, they do this by sending an e-mail to the end user.

I was thinking that maybe it could be added on a table, that way if a customer calls and say, Hey I'm at ASU on Engineering, you just look up for that on the database and you do not have to ask for directions to the customer, you can just go an tell him, May i have your building and lab please?

Just a thought

Thank you for your input

[#8]

Maybe this would work: offer the user several text fields to enter search terms. Then concatenate the address fields, and do a Contains search in that string for each of the search terms. Show all results in a list for the user to review and double-click to open a form to that record.

Another idea would work if there is a lot of repeat business. For example, the Earthquake lab may break a lot of glassware (ha, ha) and will order more on a regular basis. Then the search terms could look back in previous orders.

[#9]

Ha, ha, ha, they probably will.

Nice approach, on the search tip.