More MySQL Stuff

[Victor Subervi]

On Mon, Jun 28, 2010 at 12:11 PM, Stephen Hansen
<me+list/python at ixokai.io>wrote:

> 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.
>
> Roger on everything. Thanks again, Stephen.
beno
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-list/attachments/20100628/8fcde9da/attachment.html>