More MySQL Stuff

[Stephen Hansen]

On 6/28/10 9:10 AM, Victor Subervi wrote:
> Hi;
> So I'm launching into a major rewrite of my shopping cart because I've
> finally woken up to the challenge of injection attacks. One of my major
> problems is that many column names are determined when the shopping cart
> is built. For example, how many photos are to be uploaded is determined
> that way, thus there will be a column such as "pic1" and another "pic2"
> up as many as the client desires. Now, I guess I could cap that at, say,
> 9, and create as many columns,

Ah, you are now entering the realm of Normalization.

If you think a table requires a variable number of columns, you have 
designed the table incorrectly: no table needs a variable number of columns.

Basically, the crux of the matter is: a table does not need, and indeed 
often should not, contain every bit of detail about a certain product.

Let's say you have a basic product table: (The syntax on this may not be 
exactly MySQL-esque, so you'll have to look it up and/or adjust: I'm 
doing the SQL just as an example):

CREATE TABLE Products (
     product_sku  INTEGER PRIMARY KEY,
     product_name VARCHAR(200) NOT NULL,

     product_cost MONEY NOT NULL,
     product_description TEXT,

     ...
)

Etcetera. Here, in this table, you include everything that is general, 
generic, universal to your products.

A key important point: in no circumstance should the same piece of data 
ever be in two columns, or two tables at once (unless that piece of data 
is what's linking the two tables together-- a foreign key, but I won't 
go into that too much yet-- I don't even know if MySQL enforces 
relationships).

Now, you want to handle pictures? Okay, great, we do:

CREATE TABLE ProductPictures (
     product_sku  INTEGER NOT NULL,
     picture_num   INTEGER NOT NULL,

     picture_desc TEXT,
     picture_data IMAGE,

     PRIMARY KEY (product_sku, picture_id)
)

Now, you suddenly can have one picture per product: or a hundred. It 
doesn't matter anymore. If you want to get a list of all pictures for a 
product, you do:

SELECT picture_id, picture_desc, picture_data FROM ProductPictures WHERE 
product_sku = <sku> ORDER BY picture_id

(Also, notice that "product_sku" is the same name in every table, and 
that each table sort of has its own prefix? This is good practice. Even 
though "product_sku" in ProductPictures is in the pictures table, the 
value of that field is really a reference to a sku defined int he 
Products table).

Another point: you'll notice that in ProductPictures, the primary key is 
a composite of two fields. Picture_id's may be duplicated in this table, 
but the combination of (product_sku, picture_num) will always be unique.


> but then there's the issue of creating
> columns for all the different "mixins" that I add. For example, when the
> shop is created, if it's a jewelry store, I automatically add columns
> appropriate to the same (ring size, etc.). Now, I guess I could just
> create a table with all those columns added in irrespective of what kind
> of store it is, then hide those that aren't used when I print to screen
> such things as product descriptions or the form the client uses to
> upload his data, but that's inelegant. Any other suggestions?

It depends on just how generic you want this application to be. There's 
two approaches I've used: a pseudo-"inheritance' approach where I have a 
Product table which has the generic information, and then a 
SpecificKindOfProduct table which adds some columns: this I only use 
though in cases where I can basically pre-define the SpecificKinds, and 
I'm doing this for optimization purposes (ie, indexing and such).

So I might have like:

CREATE TABLE JewelryProduct (
     product_sku INTEGER NOT NULL,
     jewelry_ringsize INTEGER NOT NULL,

     ...
)

And such. But I only really do that if there's a finite set of 'types' 
of products the application is for (and in such cases, I *love* 
PostgreSQL's table inheritance stuff)

The other approach is to make a generic 'extra details' table, which 
looks basically like:

CREATE TABLE ProductDetails (
     product_sku   INTEGER NOT NULL,

     detail_key    VARCHAR (200) NOT NULL,
     detail_value  TEXT,

     PRIMARY KEY (product_sku, detail_key)
)

This is a very, very simple table, its basically a set of arbitrary 
key/value pairs for a given product-- its the SQL version of a 
dictionary for every product :) In fact, even when I do have 
SpecificKindOfProduct tables as I mention above, I usually have an 
'extra stuff' table here-- for extra stuff, because certain things 
always come up that just need to be noted. But don't abuse such tables 
too much, because you can't index on them as well.

The one thing I wouldn't do is make a table with a bajillion columns 
that are hidden/optional depending on what kind of store it is. Better a 
'master' table with some related smaller tables that may only be used 
for certain types of products.

-- 

    ... Stephen Hansen
    ... Also: Ixokai
    ... Mail: me+list/python (AT) ixokai (DOT) io
    ... Blog: http://meh.ixokai.io/