The Basics
Here I am going to describe to you the basic outline of the Ecommerce model and how I intend to show it can be implemented using Perl, MySQL, and Apache.
By ecommerce, I mean a system that an item can be (securely) ordered by a remote unknown user via the web. The order, along with the details or the person, are kept safe and delivered to the company for processing through normal distribution channels, similar to an order placed via telephone.
Some of the core technology behind this is the Database, the scriptint language, the web server, and the web browser.
The Web Browser
To access this system, a (potental) customer will be using their favourite web browser, and will navigate a series of forms that will return them informationtyhey request and save information they provide.
The web browser they use needs to be able to use cookies, which are basically a set of name = value pairs. In this situation, we will only use one pair, which will hold a unique value which will identify this web browser to us.
Note that I say the web browser, and not person. One person may use many different computers, and we want to take this into account. If a person purchases a new computer, or wishes to temporarily access information from an alternate location, we don't want to hassel them with having to give us all their details again!
The Web Server
The web server the the process that talks to the web browser via the network. Typically, this returns static web pages -- plain boring vanilla HTML text documents. In place of this, we are going to execute a script for each request. This script is going to examine the cookie presented by the browser (if present) and respond accordingly.
In these examples, I have chosen the scripts to be written in Perl. There are many great Perl books, but I highly recommend the OReily set -- the ones with the animals on the front. Great reading.
The Scripts
Each actual 'page' as far as the user sees is actually a script, as mentioned above. Most of the more complicated functions of the script are probably going to be used by all of the 'pages' on the site -- the functions for looking up users, find out their preferences, and fetching data from the database.
To make this easy, we are going to pack all of these functions into a perl Module, which all of our 'pages' are going to load.
The Database
Our Database is a collection of tables. A table is just like what you have seen in Word or other word processors - a set of rows and columns. Here, columns are our different things we are recording (height, weight), and a row is our different records (James, Tom). So to get Tom's weight, we get the second row and look for the column named weight.
We link our tables together using common keys or indexes. Lets cut to the chase and start looking at the records we need for ecommerce.
Database Table Design
As we said earlier, we are looking at tracking individual browsers via their cookies, so we are going to need a table for these cookies and the associated data we want about them (not the people that use them.
The Cookie Table
Firtly, so we can play with individual records, we are going to introduce a CookieID, a unique number per row so we can do things like ask for cookie 12.
Next, we want to have a copy of the cookie that will be searched for, so lets save the CookieText.
Now, this cookie is going to be one of a few that refer to a Person. The Person's details and preferences are going to be stored in aanother table, so lets imagine that we are going to have that table and have a unique key into that table, called PersonID. Since we want to link the two together, lets have a column in the Cookie Table called 'PersonID'.
Now, for our information, and so we can provide a bit more security, lets add a few more things we are interested in on the cookie level of things:
- Hostname
- The IP or name of the computer this cookie is from
- CreateDate
- When was this computer first used by this person
- LastAccessDate
- When was this computer last used by this person
- ExpiresDate
- When should we remove this cookie
This will allow us to only let the computer with the same name as that stored to use the cookie, and let us 'time out' put a time limit on the life of a cookie and remove those that havent been used for a while, ensuring that the users information is kept safe.
If/when a cookie expires, it should be able to be renewed by the user. How long a cookie lasts should be up to the user - if they are at home, it may be several months, or if they are in a web cafe, perhaps it should only last for this session!
The Person Table
This is the main place for an individual's details. The important index here is the PersonID - a unique auto-incremented field.Some of the fields we will have here are easy to understand: Name (or one for FirstName and one for LastName), Email (that's email address).
The rest of the fields we will want here are either long and complicated, have multiple parts to them, may have multiple values, or are summarise in another table. Bear with me, and I will come back to the Person table later.
The Currency Table
We are going to let people customise the values that they see on the site to their local currency. We will achieve this by having a list of currency names, currency symbols, conversion rates, and identifiers in a table. Each person will have as part of their Person record a preferred currency id, a reference to one of these currencies.
Remember, for some currencies, entity references are required (eg, Pounds Sterling.
- CurrencyID
- smallint(6) , PRI, auto_increment
- Name
- varchar(20), NULL
- Symbol
- varchar(10), NULL
- ConvRate
- double(16,4), 0.0000
- Code
- varchar(6), NULL
We have a currency code as an internationally recognised standard symbol so we may automagically match this with on-line market quotes for updates of rates!
Language
Want an international system? Well, we can database all our strings in the pages we use, and pull up the right version. For that, we need to know what language. A simple table will give us the language preferred - two columns, an ID and a Name.
Countries
Why not simplify the counrties we are dealing with. It's easy to find a pretty long list of all of the countires in the world. In place of justa list of id and Name, we are also going to add International Direct Dial (IDD) code for telephone and fax records!
Addresses
Sooner or later you are going to have to ship a product. If it is a software product, then you wont really need this, but here goes anyway...
I am defining two classes of address. They both hold the same information, but for each person, I would like to be able to keep multiple instances of each type, and have one of each type set as the default.
The two types are: Billing Address and Delivery Address. In a normal transaction, the billing address will be the shipping address. In some circumstances, such as a large multi-location organisation, there will be one central billing address for the organisation, and many delivery addresses. And lastly, sometimes there is a company that will order on other peopl's behalf - they want the goods and the billing details to go to many different addresses.
Here is my layout for this table:
- AddressID
- bigint(21), PRI, 0, auto_increment
- StreetAddress
- char(100), NULL
- PostCode
- char(20), NULL
- State
- char(40), NULL
- CountryID
- bigint(21), NULL
- Comments
- char(200), NULL
- AddressType
- enum('B','D'), D
- PersonID
- bigint(20), YES, NULL
Credit Cards
Here is the trickier part - getting financial information. Only because people are worried about abuse. ;)
To simplify this, we are going to store all but one of the fields from the users credit card. This means we have some idea of who they are, and can trace them through financial institutions if there is a problem, and further more, when the user needs to order, they wont have to enter all of their details. The trickiest bit is convincing the suer it is safe.
In this example, I am going to hang on to the card number, card type, and the name on the card, and not keep track of the expiry date at all! The user can enter this on a transaction by transaction basis.
Here is the layout, starting the an enumeration of CreditCardTypes:
- id
- smallint(6). PRI
- Name
- char(50)
And the complete credit card table:
- id
- bigint(21), PRI
- Type
- smallint(6) - a reference to CreditCardType
- NameOnCard
- char(100)
- Number
- bigint(21)
- PersonID
- bigint(21) - a reference back to Person
Telephone
Telephone and fax information is stored in the same tabel like the Billing and Delivery Addresses. Here are the fields:
- id
- bigint(21)
- AreaCode
- mediumint(9)
- Number
- int(11)
- Type
- char(1)
- PersonID
- bigint(21)
Products
Our products are pretty easy. I will organise them into categories so they are easier for people to understand and browse:
- ProductID
- bigint(21)
- Item Number
- char(20)
- Description
- varchar(100)
- Long Description
- varchar(500)
- Retail Price
- float
- Weight (in gramms or KG, depending on our product weights and freight granularity)
- int(8)
- Product Category
- smallint(4)
There is a corresponding ProductCateogry table.
Shopping Trolley
There are two tables to this: one contains information about a person's trolley (which could be in Person), and one that contains the items for all people.
- Shopping Trolley ID
- bigint(21)
- LastResetDate
- DateTime
- LastModifiedDate
- DateTime
- PersonID
- bigint(21)
And the items:
- Shopping Trolley ID
- bigint(21)
- ProductID
- bigint(21)
- Quantity
- smallint(8) - (or float?)
- Comment
- varchar(100)
The user may add a comment to the products they are ordering - ie, who/what they are for.
Summary
The above tables summarise the core parts of the system. The Perl module merely shuffles values around. ;)