I have an idea but being new to web databasing my thoughts on how to accoplish it is confusing me.

I am looking to create a customer database, nothing big here

Name
Billing Address
Phone
Fax
E-Mail

But where I get confused is adding in the products the cutomer has purchased.

BTW: I am dealing with Commercial Laundry Equipment.

I want records of Models and Serial Numbers for each customer along with date of purchase and when the warranty expires.

ie:
| Make | Model | Serial | Date Sold | Warranty Ends |
|Speed Queen|SWT121Q|0454254| 110203 | 110206 |


Seeing how customers can have many serial numbers with one model Number, I am wondering if the best way to set this up would be each customer having there own database with seperate tables for different information like addresses and products. I also want the product page to be updatable by sales to add Make, Model, Serial, ect..

Knowing as little as I do I get confused when I get to this point. Can the information be pulled from Tables combining customer info using keys or are sperate databases for each customer the way to go.

Like I said I am new, this is a project I have thought about but in no way have to complete as it is for my own curiosity, so if I am in over my head or trying to do the impossible let me know that too.

Thanks
Josh

I assume from your requirement that each customer can be associated with more than one product. If so, then you need two tables, one for customers and the other for products. The customer table needs a key like customer number, the product table needs a composite key with customer number and some product identifier.

The product identifier needs to be something that is A) unique within each customer, B) is not null, i.e. is always known at insert time, and C) will never change. If none of the current values will satisfy all of these criteria, then you need to make one up. You could use date/time inserted, or most databases allow you to define a column that automatically increments every time a new row is created.

FYI, creating a separate DB for each customer would be a flaming disaster.

Some customers do only buy one product.

But If I created two tables one with customers using the customer ID number as the primary key (KLEP60), and one with the products using the customer ID as the composite key, and the Make of the Product as the identifier (Speed Queen), could I then find all of the Speed Queen Equipment bought by KLEP60? Whether it is one peice or 100?

Your first step is to create a key for the product table that guarantees that it can be linked to customer AND maintains unique key values itself.

You do this by using 2 fields in the product table key: customer ID and another UNIQUE column.

The items you listed for product information do not appear to be unique per customer, i.e. the same customer could purchase more than 1 product with the same Make, Model, Serial, Date Sold, Warranty Date.

So first key the product table on customer ID and another key like an identity item (auto generated value).

Having done all that you can then query the products sold per customer and filter and sort the results based on any of the other columns. In other words you can view and order the results based on any of the columns, but first make sure your key structure guarantees unique, primary keys in both tables.

Based on your requirements posted, I think that you need three tables in the database. They are

Table name: Customers
Fields: CustomerID (Primary key), FirstName, LastName, Address......

Table name: Products
Fields: ProductID (Primary key), Make, Model......

Table name: Customer_Product
Fields: Customer_ProductID (Primary key), CustomerID (Foreign key), ProductID (Primary key), Serial, DateSold, WarrantyEnds......

In the table Customer_Product, you can remove Customer_ProductID as a Primary key since the combination of CustomerID (Foreign key), and ProductID (Primary key) is a primary key. But I would like to keep Customer_ProductID as a primary key since it is better for the programming.

Thanks for the information it gives me a good starting point. Like I said I am new to this so i am going to do a bit of reading and planning before I take up any more of your time. I think the hardest part will be figuring out the product key and relating it to the correct customer (for me at least) especially for multiple machines for the one customer.

Thanks again.

Josh