--001485394142403678047cd3791a
Content-Type: text/plain; charset=ISO-8859-1
Hi;
I have a shopping cart that will spawn a tmp table for every shopping cart
instance. Would it be better to create a separate database for these instead
of having them in the same database as all the other tables for the shopping
cart?
TIA,
Victor
--
The Logos has come to bear
http://logos.13gems.com/
--001485394142403678047cd3791a--
Victor,
On Sun, Jan 10, 2010 at 1:20 PM, Victor Subervi
> Hi;
> I have a shopping cart that will spawn a tmp table for every shopping cart
> instance. Would it be better to create a separate database for these instead
> of having them in the same database as all the other tables for the shopping
> cart?
It will not matter at all. But it would be better to choose a
different design. Instead of adding a table per cart, just create a
table and add a row(s) to it for every cart. This is what relational
databases were designed for :-)
Regards
Baron
--
Baron Schwartz
Percona Inc: Services and Support for MySQL
http://www.percona.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
--00235447067400a308047ce4855f
Content-Type: text/plain; charset=ISO-8859-1
On Mon, Jan 11, 2010 at 10:35 AM, Baron Schwartz
> Victor,
>
> On Sun, Jan 10, 2010 at 1:20 PM, Victor Subervi
> wrote:
> > Hi;
> > I have a shopping cart that will spawn a tmp table for every shopping
> cart
> > instance. Would it be better to create a separate database for these
> instead
> > of having them in the same database as all the other tables for the
> shopping
> > cart?
>
> It will not matter at all. But it would be better to choose a
> different design. Instead of adding a table per cart, just create a
> table and add a row(s) to it for every cart. This is what relational
> databases were designed for :-)
>
That strikes me as messy. Each tmp table has as many rows as necessary for
the products that are to be bough. To do as you say I would have to create a
table with a zillion rows to accommodate however many products I *predict*
buyers would buy. Therefore, I guess I should probably create a new database
so as to not make a mess of the main database.
TIA,
V
--00235447067400a308047ce4855f--
Victor,
> That strikes me as messy. Each tmp table has as many rows as necessary for
> the products that are to be bough. To do as you say I would have to create a
> table with a zillion rows to accommodate however many products I *predict*
> buyers would buy. Therefore, I guess I should probably create a new database
> so as to not make a mess of the main database.
You fundamentally misunderstand relational database design. I suggest
reading this book:
http://www.xaprb.com/blog/2009/08/22/a-review-of-beginning-d atabase-design-by-clare-churcher/
Regards,
Baron
--
Baron Schwartz
Percona Inc: Services and Support for MySQL
http://www.percona.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
--000e0cd5cc3e5dcb0f047ce538fe
Content-Type: text/plain; charset=ISO-8859-1
On Mon, Jan 11, 2010 at 10:49 AM, Baron Schwartz
> Victor,
>
> > That strikes me as messy. Each tmp table has as many rows as necessary
> for
> > the products that are to be bough. To do as you say I would have to
> create a
> > table with a zillion rows to accommodate however many products I
> *predict*
> > buyers would buy. Therefore, I guess I should probably create a new
> database
> > so as to not make a mess of the main database.
>
> You fundamentally misunderstand relational database design. I suggest
> reading this book:
>
> http://www.xaprb.com/blog/2009/08/22/a-review-of-beginning-d atabase-design-by-clare-churcher/
>
LOL. Ok, I'll put it on my list. *In the meantime*, since I am reworking my
database design for the shopping cart I just finished building and need to
get this up __n_o_w__, what would your advice be?
V
>
> Regards,
> Baron
>
> --
> Baron Schwartz
> Percona Inc: Services and Support for MySQL
> http://www.percona.com/
>
--
The Logos has come to bear
http://logos.13gems.com/
--000e0cd5cc3e5dcb0f047ce538fe--
--000e0cd68abc7a6ea6047ce555dc
Content-Type: text/plain; charset=ISO-8859-1
Victor,
Don't want to butt in, and not trying to be rude, but he gave you advice.
You don't seem inclined to take it. How else can he, or anyone else, help
you? Clearly you don't understand some fundamental issue about relational
databases. If you can't just accept his suggestion to put all carts in one
table as the way to do it then there really isn't anything else to say.
My 2 cents :)
keith
--
Chief Training Officer
Paragon Consulting Services
850-637-3877
On Mon, Jan 11, 2010 at 10:30 AM, Victor Subervi
> On Mon, Jan 11, 2010 at 10:49 AM, Baron Schwartz
>
> > Victor,
> >
> > > That strikes me as messy. Each tmp table has as many rows as necessary
> > for
> > > the products that are to be bough. To do as you say I would have to
> > create a
> > > table with a zillion rows to accommodate however many products I
> > *predict*
> > > buyers would buy. Therefore, I guess I should probably create a new
> > database
> > > so as to not make a mess of the main database.
> >
> > You fundamentally misunderstand relational database design. I suggest
> > reading this book:
> >
> >
> http://www.xaprb.com/blog/2009/08/22/a-review-of-beginning-d atabase-design-by-clare-churcher/
> >
>
> LOL. Ok, I'll put it on my list. *In the meantime*, since I am reworking my
> database design for the shopping cart I just finished building and need to
> get this up __n_o_w__, what would your advice be?
> V
>
> >
> > Regards,
> > Baron
> >
> > --
> > Baron Schwartz
> > Percona Inc: Services and Support for MySQL
> > http://www.percona.com/
> >
>
>
>
> --
> The Logos has come to bear
> http://logos.13gems.com/
>
--000e0cd68abc7a6ea6047ce555dc--
--001636834010376a66047ce57de5
Content-Type: text/plain; charset=ISO-8859-1
On Mon, Jan 11, 2010 at 11:38 AM, Keith Murphy
> Victor,
>
> Don't want to butt in, and not trying to be rude, but he gave you advice.
> You don't seem inclined to take it. How else can he, or anyone else, help
> you? Clearly you don't understand some fundamental issue about relational
> databases. If you can't just accept his suggestion to put all carts in one
> table as the way to do it then there really isn't anything else to say.
>
I said I would read the book. I will read it. Not today, however. Today, I
will deal with this problem, because tomorrow I will be talking with the
client. First things first.
V
--001636834010376a66047ce57de5--
--0016e6d64441ee483f047ce5957b
Content-Type: text/plain; charset=ISO-8859-1
Victor,
The temporary table solution is not a good one. Use a single table and store
each item put into a cart identified by the session ID of the user. A
process should clean out this table periodically since there are "shoppers"
that abandon carts from time to time.
The design of this table could be quite simple:
id - int auto_inc
session_id - varchar
added_dt - datetime
item_id - int
quantity - int
You could get more complicated depending in your need an item requirements
(colors, sizes, etc).
-JW
On Mon, Jan 11, 2010 at 9:30 AM, Victor Subervi
> On Mon, Jan 11, 2010 at 10:49 AM, Baron Schwartz
>
> > Victor,
> >
> > > That strikes me as messy. Each tmp table has as many rows as necessary
> > for
> > > the products that are to be bough. To do as you say I would have to
> > create a
> > > table with a zillion rows to accommodate however many products I
> > *predict*
> > > buyers would buy. Therefore, I guess I should probably create a new
> > database
> > > so as to not make a mess of the main database.
> >
> > You fundamentally misunderstand relational database design. I suggest
> > reading this book:
> >
> >
> http://www.xaprb.com/blog/2009/08/22/a-review-of-beginning-d atabase-design-by-clare-churcher/
> >
>
> LOL. Ok, I'll put it on my list. *In the meantime*, since I am reworking my
> database design for the shopping cart I just finished building and need to
> get this up __n_o_w__, what would your advice be?
> V
>
> >
> > Regards,
> > Baron
> >
> > --
> > Baron Schwartz
> > Percona Inc: Services and Support for MySQL
> > http://www.percona.com/
> >
>
>
>
> --
> The Logos has come to bear
> http://logos.13gems.com/
>
--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net
--0016e6d64441ee483f047ce5957b--
At 09:56 AM 1/11/2010, Johnny Withers wrote:
>Victor,
>
>The temporary table solution is not a good one. Use a single table and store
>each item put into a cart identified by the session ID of the user. A
>process should clean out this table periodically since there are "shoppers"
>that abandon carts from time to time.
>
>The design of this table could be quite simple:
>
>id - int auto_inc
>session_id - varchar
>added_dt - datetime
>item_id - int
>quantity - int
>
>You could get more complicated depending in your need an item requirements
>(colors, sizes, etc).
>
>-JW
I also agree with JW, a single table is better. Don't forget shoppers may
spend 30 minutes of more filling up their shopping cart and may lose the
connection to the site or have to exit without completing the order. You
need to retain the registered user's shopping cart so he can go back to it
later. Only delete it if there is no activity on it for 7-10 days. If you
use temporary tables and throw out the shopping cart contents if the
session is lost, then you will *iss off a lot of customers.
Mike
>On Mon, Jan 11, 2010 at 9:30 AM, Victor Subervi
>
>
> > On Mon, Jan 11, 2010 at 10:49 AM, Baron Schwartz
> >
> > > Victor,
> > >
> > > > That strikes me as messy. Each tmp table has as many rows as necessary
> > > for
> > > > the products that are to be bough. To do as you say I would have to
> > > create a
> > > > table with a zillion rows to accommodate however many products I
> > > *predict*
> > > > buyers would buy. Therefore, I guess I should probably create a new
> > > database
> > > > so as to not make a mess of the main database.
> > >
> > > You fundamentally misunderstand relational database design. I suggest
> > > reading this book:
> > >
> > >
> >
> http://www.xaprb.com/blog/2009/08/22/a-review-of-beginning-d atabase-design-by-clare-churcher/
> > >
> >
> > LOL. Ok, I'll put it on my list. *In the meantime*, since I am reworking my
> > database design for the shopping cart I just finished building and need to
> > get this up __n_o_w__, what would your advice be?
> > V
> >
> > >
> > > Regards,
> > > Baron
> > >
> > > --
> > > Baron Schwartz
> > > Percona Inc: Services and Support for MySQL
> > > http://www.percona.com/
> > >
> >
> >
> >
> > --
> > The Logos has come to bear
> > http://logos.13gems.com/
> >
>
>
>
>--
>-----------------------------
>Johnny Withers
>601.209.4985
>johnny@pixelated.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
--00151757739a504852047d101264
Content-Type: text/plain; charset=ISO-8859-1
On Mon, Jan 11, 2010 at 3:21 PM, mos
> At 09:56 AM 1/11/2010, Johnny Withers wrote:
>
>> Victor,
>>
>> The temporary table solution is not a good one. Use a single table and
>> store
>> each item put into a cart identified by the session ID of the user. A
>> process should clean out this table periodically since there are
>> "shoppers"
>> that abandon carts from time to time.
>>
>> The design of this table could be quite simple:
>>
>> id - int auto_inc
>> session_id - varchar
>> added_dt - datetime
>> item_id - int
>> quantity - int
>>
>> You could get more complicated depending in your need an item requirements
>> (colors, sizes, etc).
>>
>> -JW
>>
>
> I also agree with JW, a single table is better. Don't forget shoppers may
> spend 30 minutes of more filling up their shopping cart and may lose the
> connection to the site or have to exit without completing the order. You
> need to retain the registered user's shopping cart so he can go back to it
> later. Only delete it if there is no activity on it for 7-10 days. If you
> use temporary tables and throw out the shopping cart contents if the session
> is lost, then you will *iss off a lot of customers.
>
I don't see how a single table is a solution. Tables are not 3D data
structures. If they were, you'd be right! I need 2D to describe the
customer's data: cols (ID, quantity, options, etc) X products. How can I add
a third axis of custID??? No! Not possible.
I am using cookies and committing data to the temp tables every time the
customer puts something in the shopping cart.
Can someone please answer my question as to which is better, putting the
temp tables in a separate database or together with the 'workhorse' tables?
TIA,
V
--00151757739a504852047d101264--
--0016e6d7e8873c31ea047d10d82a
Content-Type: text/plain; charset=ISO-8859-1
No one designs a shopping cart system this way.
http://www.google.com/search?q=shopping+cart+database+table+ design
If you are dead set on this crazy design it doesn't matter if you put the
temp tables in the main database or another database. It'll be just as silly
either way.
JW
On Wed, Jan 13, 2010 at 12:38 PM, Victor Subervi
> On Mon, Jan 11, 2010 at 3:21 PM, mos
>
> > At 09:56 AM 1/11/2010, Johnny Withers wrote:
> >
> >> Victor,
> >>
> >> The temporary table solution is not a good one. Use a single table and
> >> store
> >> each item put into a cart identified by the session ID of the user. A
> >> process should clean out this table periodically since there are
> >> "shoppers"
> >> that abandon carts from time to time.
> >>
> >> The design of this table could be quite simple:
> >>
> >> id - int auto_inc
> >> session_id - varchar
> >> added_dt - datetime
> >> item_id - int
> >> quantity - int
> >>
> >> You could get more complicated depending in your need an item
> requirements
> >> (colors, sizes, etc).
> >>
> >> -JW
> >>
> >
> > I also agree with JW, a single table is better. Don't forget shoppers may
> > spend 30 minutes of more filling up their shopping cart and may lose the
> > connection to the site or have to exit without completing the order. You
> > need to retain the registered user's shopping cart so he can go back to
> it
> > later. Only delete it if there is no activity on it for 7-10 days. If
> you
> > use temporary tables and throw out the shopping cart contents if the
> session
> > is lost, then you will *iss off a lot of customers.
> >
>
> I don't see how a single table is a solution. Tables are not 3D data
> structures. If they were, you'd be right! I need 2D to describe the
> customer's data: cols (ID, quantity, options, etc) X products. How can I
> add
> a third axis of custID??? No! Not possible.
>
> I am using cookies and committing data to the temp tables every time the
> customer puts something in the shopping cart.
>
> Can someone please answer my question as to which is better, putting the
> temp tables in a separate database or together with the 'workhorse' tables?
> TIA,
> V
>
--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net
--0016e6d7e8873c31ea047d10d82a--
--0015175120fc295b8a047d1153c4
Content-Type: text/plain; charset=ISO-8859-1
On Wed, Jan 13, 2010 at 2:33 PM, Johnny Withers
> No one designs a shopping cart system this way.
>
> http://www.google.com/search?q=shopping+cart+database+table+ design
>
> If you are dead set on this crazy design it doesn't matter if you put the
> temp tables in the main database or another database. It'll be just as silly
> either way.
>
You're so complimentary! Wouldn't it have been better to simply suggest FKs?
Or maybe point out, as you did, how others have done it? Why be so harsh
when I simply didn't know how it's done? Thank you anyway since you showed
me how to do this.
V
--0015175120fc295b8a047d1153c4--
Victor Subervi wrote:
>
> You're so complimentary! Wouldn't it have been better to simply suggest FKs?
>
>
I think the reason the other poster was so harsh is because others have
suggested the right way to do it, if not in a lot of detail, and you
have just argued with them.
Since I'm not in the mood to work on my project I will give you an
overview of my shopping cart process and data structure.
I have the following tables.
Order
Payment
Cart
User
UserAddress
StuffToBuy
In the order table I have the following fields.
OrderID -- unique auto inc key
Status -- int to indicate if the order is in progress, processed
shipped etc. (use whatever set of statuses you need)
UserID -- this links to the user placing the order can be set to 0 if
the shopper doesn't want an account on this site.
OpenDate -- date and time the order was started (comes in handy to see
what is going on later down the road)
CloseDate -- date and time the order is finished.
TimeStamp -- auto update timestamp every time the order is modified.
(that way you can delete orders that are older than a certain age if you
like)
Any other date time fields to track with various status points are
reached depending on your needs.
Payment Table. This table exists separate from the Order table for 2
reasons. 1: I like to keep it separate. 2: if you provide the option
to pay over time for something like a service or whatever this structure
allows you to have multiple payment records for each order. Fields include.
PaymentID -- auto inc key
OrderID -- link to the order.
Status -- same as above but different uses.
Amount -- Amount of the payment.
SubmitDate -- Date and time order was submitted
ProcessedDate -- Date and time payment was processed (may be the same as
submitted if done in real time you decide if you need it)
Other Payment fields as required CC Number persons name address etc.
This way if the user making the order doesn't have an account on the
system you can store all that information in here.
TimeStamp --- just for good measure.
Cart table stores what people are buying or have bought depending on if
the associated order is closed.
CartItemID -- auto inc key
OrderID -- link to order. Before you can add an item to the cart you
need to create an order record first.
StuffID -- link to item they are buying.
Qty -- quantity they want to buy.
Price -- current price of item (save here because it can change over
time and you will want to know what they payed for it when the order was
placed.
Discount -- if you are giving some discount save that here or just leave
it as 0.
Cost -- What they will actually pay for the qty of StuffID they want.
(with Price and Discount you can see why the cost is what it is.)
DateAdded -- Date time they added the item.
You can calculate cost in a query if you like but storing it here makes
queries easier and I prefer to store an ID number for discount and look
up the amount of the discount in a discount table. Since the amount of
the discount in that record could change over time (bad idea to me but
it could happen) so that way having the final cost stored is handy. You
may want to structure it differently depending on the way discounts work
especially if there are qty discounts.
User Table
UserID -- auto inc key
UserName -- put a unique key on this. (using a separate UserID auto inc
key lets you change the user name if you want, which will happen. I let
users change their UserName on all of my web sites, don't know why
everyone doesn't do this)
FirstName
LastName
Email
Password
blah blah blah whatever you want to store about the user.
User Address this table stores various shipping and billing information
about your users so you can auto fill the payment table at the end of
the order.
UserAddressID -- auto inc key
UserID -- link to user.
AddressType -- I.E. Shipping or billing
Address Name -- so they can say if the shipping address is say for home
or work or aunt Jane's house.
AddressLine1 --
AddressLine2 --
City
State
Zip
whatever else you need like phone number etc.
Stuff To Buy table. This last table is very simplified and in most
cases would require other tables.
StuffID -- auto inc key
Name
Description
Price
QtyInStock
So now in case it's not obvious we are using foreign keys to link tables
in the following manner.
Order to Cart -- One to Many
Order to Payment -- One to Many or One to One depending on what you need.
User to Order -- One to Many
StuffToBuy to Cart One to Many
User to UserAddress One to Many
Just in case: Order to Cart -- One to Many means that "One" Order
record can link to "Many" cart records, this link is created by storing
the OrderID (foreign key) in the Cart table. Though it's not needed in
the system as described above a "Many to Many" link requires a table to
link to tables together having the two keys in it that relate to the two
tables being linked.
That's it for me tonight, everyone feel free to criticize at will.
Chris W
p.s. I don't mean to discourage the criticizing but a few notes about
the way I do things.
Keys are always auto inc. I never "attempt" to find some "real" data to
use as a unique key (very bad idea to me)
I always use the same name for my Keys in the parent and child table
(why do you think the "NATURAL JOIN" syntax exists :)
Ok let the criticizing begin :)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
--001517574572015e29047d37688a
Content-Type: text/plain; charset=ISO-8859-1
On Thu, Jan 14, 2010 at 1:35 AM, Chris W <4rfvgy7@cox.net> wrote:
> I think the reason the other poster was so harsh is because others have
> suggested the right way to do it, if not in a lot of detail, and you have
> just argued with them.
>
I don't recall anyone doing that. I don't recall arguing. I disagree. And I
don't want anyone to create an argument over this comment!
> Since I'm not in the mood to work on my project I will give you an overview
> of my shopping cart process and data structure.
>
I have copied over your excellent example and will review it for ideas.
Thanks!
V
--001517574572015e29047d37688a--