<div class="gmail_quote">On Mon, Jun 28, 2010 at 12:11 PM, Stephen Hansen <span dir="ltr"><me+list/<a href="mailto:python@ixokai.io">python@ixokai.io</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
<div class="im">On 6/28/10 9:10 AM, Victor Subervi wrote:<br>
<blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
Hi;<br>
So I'm launching into a major rewrite of my shopping cart because I've<br>
finally woken up to the challenge of injection attacks. One of my major<br>
problems is that many column names are determined when the shopping cart<br>
is built. For example, how many photos are to be uploaded is determined<br>
that way, thus there will be a column such as "pic1" and another "pic2"<br>
up as many as the client desires. Now, I guess I could cap that at, say,<br>
9, and create as many columns,<br>
</blockquote>
<br></div>
Ah, you are now entering the realm of Normalization.<br>
<br>
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.<br>
<br>
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.<br>
<br>
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):<br>
<br>
CREATE TABLE Products (<br>
product_sku INTEGER PRIMARY KEY,<br>
product_name VARCHAR(200) NOT NULL,<br>
<br>
product_cost MONEY NOT NULL,<br>
product_description TEXT,<br>
<br>
...<br>
)<br>
<br>
Etcetera. Here, in this table, you include everything that is general, generic, universal to your products.<br>
<br>
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).<br>
<br>
Now, you want to handle pictures? Okay, great, we do:<br>
<br>
CREATE TABLE ProductPictures (<br>
product_sku INTEGER NOT NULL,<br>
picture_num INTEGER NOT NULL,<br>
<br>
picture_desc TEXT,<br>
picture_data IMAGE,<br>
<br>
PRIMARY KEY (product_sku, picture_id)<br>
)<br>
<br>
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:<br>
<br>
SELECT picture_id, picture_desc, picture_data FROM ProductPictures WHERE product_sku = <sku> ORDER BY picture_id<br>
<br>
(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).<br>
<br>
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.<div class="im">
<br>
<br>
<br>
<blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
but then there's the issue of creating<br>
columns for all the different "mixins" that I add. For example, when the<br>
shop is created, if it's a jewelry store, I automatically add columns<br>
appropriate to the same (ring size, etc.). Now, I guess I could just<br>
create a table with all those columns added in irrespective of what kind<br>
of store it is, then hide those that aren't used when I print to screen<br>
such things as product descriptions or the form the client uses to<br>
upload his data, but that's inelegant. Any other suggestions?<br>
</blockquote>
<br></div>
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).<br>
<br>
So I might have like:<br>
<br>
CREATE TABLE JewelryProduct (<br>
product_sku INTEGER NOT NULL,<br>
jewelry_ringsize INTEGER NOT NULL,<br>
<br>
...<br>
)<br>
<br>
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)<br>
<br>
The other approach is to make a generic 'extra details' table, which looks basically like:<br>
<br>
CREATE TABLE ProductDetails (<br>
product_sku INTEGER NOT NULL,<br>
<br>
detail_key VARCHAR (200) NOT NULL,<br>
detail_value TEXT,<br>
<br>
PRIMARY KEY (product_sku, detail_key)<br>
)<br>
<br>
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.<br>
<br>
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.<br>
<br>
</blockquote><div>Roger on everything. Thanks again, Stephen.<br>beno<br></div></div>