Building an Online Shop's Product Detail Page [con't]
Creating a shopping cart Mechanism Â The Database
So far, we have created a fully functioning shop front for our bookshop. A user can currently view the books in their various genres, as well as select an individual book for more detailed viewing. What the user can't do is to actually buy a particular bookÂ and this is exactly the kind of functionality that we want to give our application in the next couple of articles. In this section, we are going to look at how to create the tables that we will need for the shopping cart of our online shop.
So what does a shopping cart actually do? A shopping cart must fulfill these basic requirements:
- Allow the customer to add items to the cart
- Allow for different quantities of each item
- Allow the customer to alter the quantities of an item
- Allow the customer to remove items from the cart
These are only the basic requirements of a typical shopping cart, you might add more depending on your needs.
The Database Tables
- Somehow identify the user
- Hold the name and other information of the books that the user wants to buy
- Hold the quantity of the books that the user wants to buy.
- Store the date on which the user intends to buy a book.
Now, because I did not create a login script for this online shop, we need to find another way to identify a user of our online shop. The only other way, in the absence of a login script, is to use sessions. Sessions are started simply by calling the session_start() function and when you call this function a session ID is generated. The sessionID has about 32 alphanumeric characters. This id lasts throughout the execution time of a particular script. So far, we have worked out that we need:
- To identify the user that is currently browsing the books
- Provide the names of the books that the user wants to buy
- Store the quantity of books that a user wants to buy
This should give us some indication of what kind of table we should create to track a user's purchases. It should go something like this:
While the table structure above meets the requirements for tracking a user, it needs some optimization. For example, we know that databases work faster when dealing with numbers instead of text, therefore, if we remove all the information about a book (i.e the fields named books_*) that the user intends to buy and simply add the book id to the table, it will work equally effective but faster. Once we have the book id, we can simply run a query and retrieve all the information about the book from the books table. Therefore, the revised table should have the following fields:
Why the "date_added" field? This is just to strengthen the method of identifying the user. If we use the sessionID and date, we can identify the user more accurately. Now, copy and paste the following to create the cart_track table:
With the following sample data:
The second requirement for a shopping cart is when the user decides to checkout, at which point you should take the users name, mailing address and credit card details. We need a table that will hold a record of all of the data (except credit card numbers). A rough outline of the table would look something like this:
NOTE: In this application, we are not going to deal with actual payment of the order. See the Payments section below.
The outline above more than adequately meets the requirements; but please feel free to add more fields to suit. Our final table will look something like this:
Some sample data:
Guidelines for online Payment
Because there is such a vast array of payment systems out there, I've decided to leave that part out and to say just a few words about it. Several commerce methods exist when it comes time to pay for the purchases in the shopping cart. The "right" method for you depends on your business. Merchant accounts through banking institutions often require you to have a business license, a reseller's permit, and other pieces of paper proving you're a legitimate business. If you're simply a person who has a few items to sell, you might not want to go through all that paperwork. However, you still have options!
Regardless of the payment method you choose, one thing is certain: If you are passing credit card information over the Web, you must do so over an SSL connection. You do not have to use this secure connection during the user's entire shopping experience, just from the point at which sensitive information is captured, such as the checkout form.
Additional, fields for the "orders" table should include the credit card number, expiration date, and the name on the credit card, if you are going to implement a payment system. Another nice feature is to repeat the user's shopping cart contents with an item subtotal, so the customer remembers what he's paying for and approximately how much the order will cost. Also at this point of the checkout sequence, you offer any shipping options you might have. Shipping and sales tax would be calculated in the next step of the process.
From the point of clicking the submit button on the form, the checkout sequence depends on the payment method you are using. VeriSign's product is one of several transaction-processing gateways that exist for use by merchants. Your bank will usually provide a list of merchants it prefers you to use. If you stray from your bank's list of preferred vendors, be sure to research your selected vendor thoroughly, to avoid any delays with deposits and to ensure you're getting the best deal.
In this article we discussed the requirements for a shopping cart as well as its functions. We also created database tables to cater for those requirements. While creating these tables, we went through the normalization process to optimize and make the processing of data faster. In the next article, we will discuss in detail how to integrate the shop front application with the shopping cart. We will also look at the scripts that make the shopping cart work.
Original: January 8, 2010