Mapping shopping actions to tables

[SaranacLake]

Let's say that I have a standard e-commerce setup with the following back-end table structure...

	CUSTOMER --||-----0<-- ORDERS --||-----|<-- ORDER_DETAILS -->0-----||-- PRODUCTS
	

 

This may sound dumb, but I'm trying to understand how actions that the user takes while shopping would map to the back-end tables.

 

For instance, when would I start populating these tables?

Would I start populating the tables as the Customer is adding items to his/her shopping cart?  (Or do I need extra tables for that?)

Or would I populate all of the tables at the very end when the Customer submits the order?

Or somewhere in between, like when he/she chooses to checkout?

 

I buy thousands of dollars a year on e-commerce sites across the Internet, but that doesn't mean I necessarily understand all of the inner-workings of what happens behind the scenes!

Can someone help me get a better grasp on all of this?

 

 

[requinix]

3 hours ago, SaranacLake said:

Would I start populating the tables as the Customer is adding items to his/her shopping cart?  (Or do I need extra tables for that?)

You could, if you wanted to make it persistent. "Saved cart" sort of thing. Most of the time a temporary cart is fine so sites store the cart in the session.

3 hours ago, SaranacLake said:

Or would I populate all of the tables at the very end when the Customer submits the order?

It's unlikely that you could wait to put anything in the database until the very end. More likely is that you will eventually need to store the order in a "pending" state, to be confirmed later when the payment goes through or something.

3 hours ago, SaranacLake said:

Or somewhere in between, like when he/she chooses to checkout?

That depends on when you want to empty the cart. Typically the cart remains a cart until near the end of the checkout process when it becomes a pending order; that way if the user quits their cart is still intact, they can re-checkout, and you don't have extra unused information floating in the database.

On that note, something to keep in mind is how the system should behave if the user aborts in the middle of the process.

[SaranacLake]

14 hours ago, requinix said:

You could, if you wanted to make it persistent. "Saved cart" sort of thing. Most of the time a temporary cart is fine so sites store the cart in the session.

So as the customer shops, I would just store the items they choose and the quantities in the $_SESSION?

 

14 hours ago, requinix said:

It's unlikely that you could wait to put anything in the database until the very end. More likely is that you will eventually need to store the order in a "pending" state, to be confirmed later when the payment goes through or something.

So at what point do you create an Order and populate the Order table?

It wouldn't make sense to do that as a person is still shopping.

I guess when they click "Proceed to Checkout" I could create the Order record then.

But is there any harm in waiting until the click "Process Payment"?

I guess one benefit of creating an Order record and child Order_Details records once they click "Proceed to Checkout" is that it is similar to having a persistent Shopping cart.

 

14 hours ago, requinix said:

That depends on when you want to empty the cart. Typically the cart remains a cart until near the end of the checkout process when it becomes a pending order; that way if the user quits their cart is still intact, they can re-checkout, and you don't have extra unused information floating in the database.

On that note, something to keep in mind is how the system should behave if the user aborts in the middle of the process.

What do you recommend?

[requinix]

38 minutes ago, SaranacLake said:

So as the customer shops, I would just store the items they choose and the quantities in the $_SESSION?

Yeah.

Quote

It wouldn't make sense to do that as a person is still shopping.

I guess when they click "Proceed to Checkout" I could create the Order record then.

Sounds good.

Quote

But is there any harm in waiting until the click "Process Payment"?

Sounds good.

Quote

I guess one benefit of creating an Order record and child Order_Details records once they click "Proceed to Checkout" is that it is similar to having a persistent Shopping cart.

Sounds good.

[SaranacLake]

12 minutes ago, requinix said:

Yeah.

Sounds good.

Sounds good.

Sounds good.

 

😋

[requinix]

The best way to learn something is to do it, even if it turns out to be a little bit wrong. As long as it's not too wrong, it's good enough to last a while. You can always change things later.

[SaranacLake]

18 minutes ago, requinix said:

The best way to learn something is to do it, even if it turns out to be a little bit wrong. As long as it's not too wrong, it's good enough to last a while. You can always change things later.

Alas that is the tricky thing about pouring concrete...

Are the footings (and hole) "close enough" to where they should be, or am I gonna be crying when I realize my foundation/driveway is insufficient?!

I see your point, but it's a fine line...

 

 

[requinix]

1 minute ago, SaranacLake said:

Alas that is the tricky thing about pouring concrete...

Are the footings (and hole) "close enough" to where they should be, or am I gonna be crying when I realize my foundation/driveway is insufficient?!

As long as you can park on it, it's good enough. Even if it has some cracks. Or isn't exactly even. Let it sit for a while, use it, and later you can decide what is wrong and what you need to do to fix it. Then next time when you need to pave a walkway you'll have a better idea of what to do.

[SaranacLake]

5 minutes ago, requinix said:

As long as you can park on it, it's good enough. Even if it has some cracks. Or isn't exactly even. Let it sit for a while, use it, and later you can decide what is wrong and what you need to do to fix it. Then next time when you need to pave a walkway you'll have a better idea of what to do.

I agree other than I have made a long career of working for large companies that had poor design and they pay the price the rest of their lives because it is less painful to keep duct-taping crappy design than pull the plug and start over and do it right.

Since I am a company of one, I fear really fubar'ing my design and never being able to recover.

But I get your gentle "Analysis Paralysis" *hint*  🙂

[requinix]

10 minutes ago, SaranacLake said:

Since I am a company of one, I fear really fubar'ing my design and never being able to recover.

There is that risk, yes. But what I haven't said is that you're really the only one here who knows your application best. There is no one-size-fits all answer to stuff like ecommerce. No one can just tell you what to do and be totally right about it: every company needs something a little different, and when random people on the internet give advice some of it will be right and some of it will be wrong.

I also don't think I mentioned but your questions are (mostly) not technical questions. They're business questions. Application requirements. So what you should do is figure out, as best as you can, what the requirements are and how to go about satisfying them. Is there a requirement that an order exist in the system before the user submitted payment? Is there a requirement that a cart be saved in an account instead of temporarily held in the browser? Create a specification that describes everything you need to support, then make sure what you code supports it.

[SaranacLake]

5 minutes ago, requinix said:

There is that risk, yes. But what I haven't said is that you're really the only one here who knows your application best. There is no one-size-fits all answer to stuff like ecommerce. No one can just tell you what to do and be totally right about it: every company needs something a little different, and when random people on the internet give advice some of it will be right and some of it will be wrong.

I wouldn't be here if I didn't think there were people here smarter than myself - I'm talking to one now.

But, yeah, that is also why I'm being a nervous-nelly and being skeptical of advice I take and decisions I make.

Fwiw, I am *very* confident in what I have designed so far, just wish it wouldn't have taken me years to come up with?!

If I can just get my e-commerce module done, then I'm home free and get "go live" and face the larger challenge which is, "is anyone willing to part with their $$ to buy what I'm selling?"

Film at 11...  😎

 

5 minutes ago, requinix said:

I also don't think I mentioned but your questions are (mostly) not technical questions. They're business questions. Application requirements. So what you should do is figure out, as best as you can, what the requirements are and how to go about satisfying them. Is there a requirement that an order exist in the system before the user submitted payment? Is there a requirement that a cart be saved in an account instead of temporarily held in the browser? Create a specification that describes everything you need to support, then make sure what you code supports it.

Yes, I understand and agree.  But this circles back to my challenges with design...

I'm not always 100% certain what I need (or want).

Most of the time I have a "vision" of what I want, but sometimes I'm not sure how things work in the real world or what others commonly do or what makes good sense, and so I end up with questions like today.

I'm trying my best, but this stuff is complicated!!!  ☹️