Prev: Python 2.6.x version module compatibility
Next: CONTROLLED DEMOLITION INC explosive-charge placement technician ?Tom ?Sullivan 911 TESTIMONIAL Video
From: Stephen Hansen on 28 Jun 2010 12:41
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/

From: Emile van Sebille on 28 Jun 2010 14:54
On 6/28/2010 9:10 AM Victor Subervi said...
> Any other suggestions?


http://www.databaseanswers.org/tutorial4_db_schema/index.htm

 | 
Pages: 1
Prev: Python 2.6.x version module compatibility
Next: CONTROLLED DEMOLITION INC explosive-charge placement technician ?Tom ?Sullivan 911 TESTIMONIAL Video