On Mon, Jun 28, 2010 at 12:11 PM, Stephen Hansen <me+list/pyt...@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
-- http://mail.python.org/mailman/listinfo/python-list