Database Structure Query

[DeskMonkey]

Hi all,

New to both Access and this Forum, but after putting it off for so long, have now decided to try to get my head around Databases. My first project is to build a Database that enables me to search a customers past orders with my company. What I need to know firstly, is how best to structure the database. These are what I have so far (trying to migrate from Excel):

Table 1:
Customer ID (PK)
Customer Name
Customer Address 1
Customer Address 2
Post Code
Phone Number.

Table 2:
Product Code (PK)
Product Description
Pack Size
Case Size

So, table one is essentially a customer list and table 2 is essentially a product list. Keeping the 2 entirely seperate makes complete sense, but how should I go about recording each customer's purchase history. Obv Each customer will buy many products - some of which will be on several customer's histories.

Do I create a seperate table for each customer with their history on? That seems to make sense, but equally, it seems to go against all the fundamentals of database design.

I am doing this with a view to incorporating several other elements such as Customer complaints, enquiries, etc later down the line. For now though, my goal is to be able to type the Customer Code and a Keyword into a search query and have all the items a customer has bought containing that keyword returned in the results.

I would appreciate any help on this - and would also appreciate any pointers to good tutorials. I am self taoght in Excel and reasonably competant, so am confident I can pick up Access once I get my head around the principles and how it differs to Excel.

Many thanks in advance!

 

[pbaldy]

Standard normalized design would be an "Order Header" table that contained an order ID field (PK), your customer ID field (related one-to-many with the customer table) and fields for date, etc. You would have an "Order Details" table with fields for Order ID (related one-to-many with the header table), product ID (related one-to-many with the product table), quantity, price, etc.

From those tables you can extract the orders or products for any customer, date, etc.

 

[jzwp22]

Do I create a seperate table for each customer with their history on?

No

Since a customer can place many orders, that is a one-to-many relationship, so you will need a table for for that

tblCustomerOrders
-pkCustOrderID primary key, autonumber (pk=primary key)
-fkCustomerID foreign key to tblCustomers (fk=foreign key)
-dteOrder (order date; dte=date/time datatype)

A customer order can have many products, so a one-to-many relationship. Also, a product can be part of many orders, so another one-to-many relationship. When you have 2 one-to-many relationships between the same two tables (orders and products) you have a many-to-many relationship which is handled with a junction table as follows

tblOrderDetails
-pkOrderDetailID primary key, autonumber
-fkCustOrderID foreign key to tblCustomerOrders
-fkProductID foreign key to tblProducts
-lngQty (long integer number datatype assuming quantites are whole numbers)

It is generally recommended to not have spaces or special characters in your table or field names otherwise you have to enclose each name in square brackets which is a real hassle and adds a lot of extra typing and it confuses Access if you do not include the square brackets.

This site has some tutorials for someone just starting out in Access that might be useful for you.

oops, I see Paul beat me to the punch.

 

[DeskMonkey]

No

Since a customer can place many orders, that is a one-to-many relationship, so you will need a table for for that

I have to admit I am still a little lost - and it ocurred, maybe it is because I haven't expressed myself clearly?

Customers will not place any orders! What I am creating is a static list of products a customer purchases. This database would be purely a reference tool.

The end result would be a list of 500 customers - each with an individually taylored list of products they purchase.

So, say customer 1 calls and says I'll have three pens. I can Type "CUS1" and "Pen" into my search query and it will return the pens I have put on the customers 'product file.' In a nutshell:

• A customer can have many products on his history.
• A product can belong to many customers order histories.
• A customer Will olny have one order history.

Am I right in thinking I need something a little simpler than has been suggested previously.

I should say thank you both immensely for your fast and helpful replies. I will browse the link you gave and work throgh it. If I can crack the whole relational thing, the rest will come fairly easily I'm sure...

(touches wood!)

 

[jzwp22]

Sorry for the misunderstanding, and you are correct, the structure will be more simple, but the same basic principle applies as you say:

A customer can have many products on his history. (one-to-many relationship)
A product can belong to many customers order histories. (one-to-many relationship)

Two one-to-many relationships between the same two entities (customers and products) requires a junction table.

So your original tables

Table 1:
Customer ID (PK)
Customer Name
Customer Address 1
Customer Address 2
Post Code
Phone Number.

Table 2:
Product Code (PK)
Product Description
Pack Size
Case Size


And now the junction table:

tblCustomerProducts
-pkCustProdID primary key autonumber
-CustomerID foreign key to table 1
-ProductCode foreign key to table 2

With respect to forms, you can have a main form based on your customer table with a subform based on the junction table. The subform would have a combo box based on the product table. In terms of searches, I generally set up a form (not bound to a table) that presents selections (generally list or combo boxes) to the user. After the user makes a selection, open a form to the record or records that meet the criteria.

 

[DeskMonkey]

Thanks so much for the clarification:

I have attached a sample of what I have so far if you wouldn't mind having a look at it. I'll be honest, I'm still really struggling with the relationship side of things.

In my mind when I started this, I would create a order history for each customer - and each order History would have a unique Autogenerated ID. As it stands, when I go into the "tblJunction" to start entering products, each product I enter has a unique autogenerated "History ID"

Would this be correct? That would mean I have to enter the Customer Code against each product line entered.

If I choose to build the history via "tblCustomer", whilst I wouldn't have to enter the Customer ID in each field, I would still have a unique HistoryID number for each product.

Also, for each product on a history, I would like to add a field that states whether the customer usually purchases the product by the case, or individually.

Thus, when a user searches for "Cus1" and "Black", a result string similar to this would appear:

Product ID | Description |Size | Pack | Usual Ord Quantity
PRD1 | Black Pen |M | 5 | Case

What I want to do is really simple, but I'm finding it really hard to visualise how to structure the whole thing. Tutorials seem conflicting sometimes too - esp about which fields to make Primary and Foreign keys in the Junction Table.

If anyone has the time and/or the inclination to spell out in terms a 3 year old could understand where I may be going wrong, my brain sure would love you!

Once again, thanks for all your help thus far.

 

[jzwp22]

In my mind when I started this, I would create a order history for each customer - and each order History would have a unique Autogenerated ID. As it stands, when I go into the "tblJunction" to start entering products, each product I enter has a unique autogenerated "History ID".

The purpose of the autonumber datatype is to just assign a unique identifier to each record in the table. This is typically the function of a primary key--to uniquely identify each record. The product is not getting the new ID, the record is. The record associates the product and the customer. How you have the HistoryID set up is fine, but you should not have the product and customer fields in the junction table set as primary keys. The autonumber field should be the primary key since it is unique. Also some records will have the same product code (but different customer codes) so you have to allow duplicates. The same would be true for the customer code field. I went ahead and made those changes in the attached DB

That would mean I have to enter the Customer Code against each product line entered

Technically you have to enter the customer code and the product code to create a record in the junction table. If you leave one of the two fields blank, Access should throw back an error. The reason this happens is because you have enforced referential integrity which is the correct thing to do since you do not want to have records in the junction table that indicate a customer without a product or a product that is not tied to a customer. This would be called an orphan record.

Typically data entry is done through forms not by entering table directly in the tables. Using forms, Access will automatically fill in one of the two fields (product or customer) for you based on the relationships you have already set up. With respect to forms, you would typically base a main form on the one side of a one-to-many relationship, and then you would have a subform in that main form. That subform would be based on the table that makes up the many side of the relationship (the junction table in this case). When you have a junction table setup as you do, you would use a combo box to supply data for the other field.

I have created a sample main form based on the customer table with a subform based on the junction table in the attached DB.

If I choose to build the history via "tblCustomer", whilst I wouldn't have to enter the Customer ID in each field, I would still have a unique HistoryID number for each product.

Since you have it set up as autonumber, Access will take care of it, you do not have to do anything.

Also, for each product on a history, I would like to add a field that states whether the customer usually purchases the product by the case, or individually.

You would have to add this field to your junction table to take care of this. In fact, any information that relates to a product a customer orders/uses should be in the junction table or a separate table related to the junction table (it will depend on what type of information you may want to capture). If something only deals with the product, independent from a customer, then it goes in the product table.

...esp about which fields to make Primary and Foreign keys in the Junction Table.

The primary key field of a table must hold a unique value for each record in that table. That is why I typically have an autonumber field in every table and it is always the primary key. You can choose to have another field as the primary key such as you have for ProductID and customer ID in their respective tables but the value in that field must be unique.

Many people feel that the primary key should have no meaning to the user and in fact, the user should never see the key value. Of course, others feel the opposite.

Since I always use the autonumber as the primary key, it does not have any meaning to the user.

Now as to foreign keys, that is what you have in the junction table for both the product and customer ID fields. The matching of the primary key of one table to its corresponding field (the foreign key) in another table is what makes the relationship between tables. If you did not have the customer ID field in the junction table you would not be able to relate a customer to their products. The primary and its matching foreign key must be of the same datatype. In your case the customerID field of the customer table and the customerID in the junction table are both text fields. The same is true for the productID fields.

If you use an autonumber primary key, the matching datatype would be a long integer number. The autonumber is just a special case of a long integer number.

Just as a note, typically the primary key and foreign key fields are numeric since relational databases are more efficient with numbers compared to text.

 

[laolas]

I have created a sample main form based on the customer table with a subform based on the junction table in the attached DB.

 

[jzwp22]

laolas,

I did not see an attachment. I was also curious as to why you were posting a database. Did you have a question or were you provided an example database for the person who originally started this thread?