Welcome to WebmasterWorld Guest from

Forum Moderators: buckworks

Message Too Old, No Replies

osCommerce database as a reference

question about the customers and orders table



6:08 pm on Jul 5, 2004 (gmt 0)

10+ Year Member

Hi there

I'm just looking at the osCommerce database model to help me create my own and I'm wondering how the delivery address details work.....

I will have the cardholders address permanently stored in the Customers table. But the Customers table does not store the delivery address as that is subject to change on each order....

Looking at the osCommerce model they have the delivery address and credit card details stored in the Orders table, which is fine. But I'm also assuming that nothing enters the Orders table until the order is officially submitted. Is this correct? If so, how does the user check the order before submitting? Its usual for the user to input all the details and click a proceed button which will direct them to the summary of an order (products, prices, credit card details, cardholder address, delivery address etc). The customer will then check everything and submit, right?.....

So, if none of the order details have entered the Orders table prior to the order confirmation then how is the order summary displayed on screen? Or do the details enter the Orders table but are deleted if the order is not confirmed?

An explanation would be very much appreciated. Many thanks :)


6:29 pm on Jul 5, 2004 (gmt 0)

WebmasterWorld Senior Member lorax is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

Is this correct? If so, how does the user check the order before submitting?

That is correct. The variables for the order are by using a session. When the times comes for check out the customer is given one last chance to review their order after the shipping info has been submitted. Once the form is submitted, the credit card is checked and charged, and the order is completed.


6:34 pm on Jul 5, 2004 (gmt 0)

10+ Year Member

Thanks very much for the reply. So all the delivery details (even the credit card details) are held in a session? I take it the credit card details are encrypted in the session? Or are regular session variables safe?

Many thanks indeed :)


8:18 pm on Jul 5, 2004 (gmt 0)

10+ Year Member

Sheer madness... I just took a look at some of their source code... I'd be too scared to run this for my own store.

BigHit, if you ever want to put your code in production, do make sure you have a db that can handle transactions.


2:41 am on Jul 6, 2004 (gmt 0)

WebmasterWorld Senior Member lorax is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

>> too scared to run this for my own store

I understand your fear. It's a bit scary but rest assured it works well. There are MANY installations os osCommerce out there. Bare in mind many of those have undergone some significant customizations but - nonetheless - they started as osComm and still have some osComm in them.

re: CC# in a session Yes. Is it encrypted? I don't think so. Session vars are safe - in general - unless they're hijacked by someone. That's unlikely but possible I suppose.


3:45 am on Jul 6, 2004 (gmt 0)

10+ Year Member

lorax- Take a look at checkout_process.php

Now imagine things don't quite work like you'd expect them to. Suppose for some unknown reason, halfway through the process of entering an order, Apache or the database crashes, you lose a connection, power, are unable to send confirmation emails, whatever.

Going through that checkout code, there is no point at which it would seem benign to have an error. Because the underlying db is MySQL this was in use without transactions. If you are using the inventory tracking, an order could be placed and inventory not substracted. With transactions, it's all or nothing -either everything works or you don't do a thing, so there's no corrupt data to deal with.

There are a lot of other similar issues that just scare me. The fact that the code does not enforce a good separation of concerns make it difficult to maintain (there's HTML and SQL in the same file!). Although they mention in their "development workboard" that they plan to make more use of objects, you can see that milestone 3 has been almost a year in the making. This suggests to me that maintenance is slow, likely because of the state of the codebase.

Though I guess that's all a bit off topic :(


5:08 am on Jul 6, 2004 (gmt 0)

10+ Year Member

I love when people actually notice that osCommerce is not that great. Free!= great.


7:10 am on Jul 6, 2004 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member


so what do you suggest :)


8:22 am on Jul 6, 2004 (gmt 0)

10+ Year Member

OS Commerce code is a true mess; nevertheless, it is a good starting point for people who want to begin their first e-commerce carrier :)

If I properly understtod you, BigHit, the explanation is:

1) User have to log into his account prior to checkout.
2) If user does not an account prior to login, he has to create an account and give at least one address.
3) That address is then stored in 'address_book' table, and then 'address_book_id' entry value from 'address_book' table is stored as 'customers_default_address_id' in 'customers' table.
4) During checkout process, the entry number 'customers_default_address_id' is read from 'address_book' table and offered as a default delivery/payment address.

How CC data is processed depends on actual payment method module -- it can either be stored in session variables, or CGI variables.

After user has commited an order, all the data is written into 'orders' table -- as real data, not as a reference to an another table.


8:38 am on Jul 6, 2004 (gmt 0)

10+ Year Member


I feel if I'd create a shopping cart software, it probably would me not much better as OSC itself :(

Could you provide a reference to something like "design patterns" for web/db programming -- a book, or an URL, or an example of a well-designed/coded system (with open code)?


9:47 am on Jul 6, 2004 (gmt 0)

10+ Year Member

Thanks very much for your help guys :) I'm now very close to having a usuable database, so I'll start to build the site after a bit more modelling.

One last thing which I'm unsure about.....

Initially I'm going to have 1 Product table and 1 ProductOptions table which will contain all the different variations of a product and somply looks like this:

optionID - PK
productID - FK

So, at the moment, in my ShoppingCart table I have this:

cartID - PK
productID - FK and PK
optionID - FK
customerID - FK

I'm pretty sure that won't work though because if a customer adds a bike with the option Small/Red and then adds the same bike with the option Medium/Red, its going to increment the value, yes?

So do I need to incorporate the optionID into the ShoppingCart primary key and not have the cartID as part of the primary key? Or do I need to do something different with my options table? Or any other table for that matter?

Sorry for all the questions, I just need to be very comfortable with this rather than rush into it.

Many thanks :)


10:07 am on Jul 6, 2004 (gmt 0)

10+ Year Member

I'd rather had Products table, Options table and Products_to_Options -- to have possibility to combine different options for one product.

And cart something like:
cartID - PK
productID - FK
optionID - FK
customerID - FK

I think you can have cartID as PK, and all other as FK.
If you add something to cart, cartID will increment automatically.

Just check if customer adds same product with same option one more time to cart, and increase quantity rather than add one more product.


1:16 pm on Jul 6, 2004 (gmt 0)

10+ Year Member

If a product can have more than one option, you'll need a cart_options table.

Also, if the same product can be added twice with two different options, you can't have the PK being (cartID, productID).


1:43 pm on Jul 6, 2004 (gmt 0)

10+ Year Member

thanks again to you both for your help :)

danieljean, would you mind taking a look at my datamodel? I'm not allowed to post links to the images on here but I can send you a sticky. No prob if you are busy, but if you get chance that would be great!

Many thanks


1:57 pm on Jul 6, 2004 (gmt 0)

10+ Year Member

hotwheel- true, though keep in mind too that
Expensive!= great

I'm strongly leaning towards releasing my cart under a GPL license. It's nowhere near as feature-packed as OSC, and it still needs better architecture. BigHit- any chance you'll release your code? :)

Morgenhund: The book "Design Patterns" and the documentation for Struts is what I used, along with various articles. If I had to start over, I might begin with the book "Web Application Architecture: Principles, Protocols and Practices"; you might want to look at some of Sun's sample applications. Can anyone else suggest a good resource?

That said, at minimum a good application would cleanly separate data, logic and presentation. A designer should be able to modify any aspect of the appearance of a site without ever touching anything dealing with application logic or SQL. And a programmer should not have to deal with HTML when a designer does that job. All of that is impossible with OSCommerce :(


2:12 pm on Jul 6, 2004 (gmt 0)

WebmasterWorld Senior Member lorax is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

I agree it's not the best code in the world. But take it for what it is. A project tool offered by a bunch of programmers with the intent of offering something useful to would-be ecommerce webmasters. :) My point was that there are many installations of osCommerce out there. Some of these are fairly large stores and many are not but they are succesful and working well. As an entry point into the ecomm world, osCommerce is a reasonable option. Granted I would never use it straight out of the box as there are somethings that it lacks for even a basic cart. But nontheless, it's a good option for those who want a flexible solution and are capable of wrapping their minds around the code. Is it the best cart out there? No. Is it a viable option? Yes.


2:37 pm on Jul 6, 2004 (gmt 0)

10+ Year Member

BigHit- any chance you'll release your code?

Well, I don't actually think it would be of much use to anyone as it will be my first attempt at my own E-commerce solution. Plus I've only had a couple of months of good experience with .NET. Prior to that I hadn't done any web programming :( I'll see how it goes, but I'm learning most of it as I go along.

Personally, I prefer to do the design as well :) I enjoy playing about with CSS and so I can completely separate presentation from content.


7:09 pm on Jul 11, 2004 (gmt 0)

10+ Year Member

danieljean : I think we all know that expensive != great. I think expensive is also a relative term.

I am sure I could rattle off 10 things about each of the market leaders that make it undesirable like Websphere or ATG. However, I could do 20+ about most open source apps.


1:17 am on Jul 13, 2004 (gmt 0)

10+ Year Member

Hmmm.... I was drafted into creating an e-com package for my parents business.
As a joint Physical/Computer Sciences student it was a bit of a change from my standard experience, but fun in-and-of itself.

Ultimately, the target was simple:
A Brick & Mortar store was loosing their site and needed a new one within 2 months. Previously they had an e-com section, but all items in it were part of the host's setup.

So, a re-design, a quick & dirty site, then a full-blown one. But still, no merchant account, and probably no orders... but just to have the feature there. And more accurately, the physical inventory that they currently had be browsable. Of course, it did have to be secured.

Also, shared hosting, not dedicated. Dangerous, but there are simple ways arround those dangers. :~) But the most demanding thing was not to have the main inventory online, but rather in a program called BookManager (FYI, bookmanager is a DOS power-user program, with all content aimed at power-users, and worst of all, closed source - the only way to extract the db was with CSV, unless I wanted to dabble in reverse-engineering *shudder @ DMCA*). Updated (over writen) online weekly though.

Anyways, everything ran under a Class header with title's like BOOK-FICTION. (and yes, EVERYTHING WAS IN UPPERCASE AND THERE WAS NO WAY TO DO AN INTELLIGENT CAPITALIZATION. Also, lots of stuff was abbrviatd).

So, trying to think of a package to meet these specs drew me a blank, so I started from scratch.

Database Store:
... Item Groups and Bookshelves. A bookshelf is defined as an object capable of holding Item Groups, Individual Items, or other Bookshelves.

Database Info:
... General info, members, past orders, etc.

Database Credit:
... Holds Credit card info. Database info encrypted, and no access passwords stored... anywhere. :~)

Anyways, the amazing thing is I did it all. Took about 4 months, mixed in with study time, and learning PHP. I know, I suck. A good coder could do it in, what? three weeks?

Used Java to convert/filter/... the CSV file into an SQL file. (Which I shall make Free, as in beer).

Anyways, I got $2g for it, not that great, but hey when working under parents, you usually get nothing. And I got to keep the rights. Anyways, rewriting it to make use of some database classes I wrote (I don't like the other's I've seen), and ultimately cut out all global functions. If anyone thinks it should be Free (maybe not beer this time), I probably will make it so. Or maybe I'll put it on a digital shelf for a rainy grave.


Featured Threads

Hot Threads This Week

Hot Threads This Month