On Jan 14, 2009, at 1:39 PM, James Brouwer wrote:
Walter,
Great approach, thank you. It makes perfect sense, though it raises
another table/field layout question.
We’re planning on having roughly 4 photos per shoe style (here a
manufacturers style would be distinctive for each color and
material, as well as their other features). For styles in which we
happen to have multiple pairs, we would only take representative
photos of one pair.
I’m trying to minimize the workload of entering redundant data into
the Shoes database as well as avoiding unnecessary null fields.
Clearly, entering the same photos multiple times for different sizes
in the same table doesn’t make sense. Would the proper way to go be
to set up an Inventory table with a primary key such as
SKU# to hold each pair of shoes by size (with length and
width fields) using a foreign key such as Mfg#? The related
Descriptions table would hold Mfg# as the primary key
and have the corresponding fields that would describe a particular
style (including all of the photos). Does that make sense?
Add a table called photos. Give each photo an id, and in each shoe
that has a photo, have a matching photos_id.
As far as the interface for adding product goes, I would make it
possible to add multiple sizes in one pass. Make an interface with all
the fields for the shoe – name, description, materials, whatever.
Then add one field called size[] (square brackets are important here)
and another called width[] and another called stock[]. Use a
JavaScript to add more copies of these fields as needed.
When your form is submitted, all of those sizes and widths are going
to appear as arrays, so you can use that as a way to generate shoes in
the database without having to enter each size over and over. (The
following example uses the MyActiveRecord object-relational mapper for
brevity, you can adjust this to match your own method of creating and
saving rows in the database.)
foreach($_POST['size'] as $k=>$size){
$shoe = MyActiveRecord::Create('Shoes');
$shoe->populate($_POST);
$shoe->size = $size;
$shoe->width = $_POST['width'][$k];
$shoe->stock = $_POST['stock'][$k];
$shoe->save();
}
So now, with one post, you have added as many different sizes as you
want, each as a separate row, and each with its own stock total. You
could have added 20 x 9.5 D, 15 x 9 E, whatever. It will be one post.
All of these shoes will have exactly the same properties set for name
and description and whatever else. If your form included an upload for
the photo, you could create the photo, get its id in a variable called
$photo_id, and then set that as the photos_id in each shoe record,
just by adding $shoe->photos_id = $photo_id; before the call to
save();
Taking it a step further, could we reduce overall storage size (and
redundant data entry) by developing a number of tables to lay out
all of the other redundant information such as Manufacturer Name,
Color, Material, Style, Feature, etc.? Or would such a schema of
pulling in multiple tables wind up just slowing server response time
down (trading off storage space reduction for performance hits)?
In my experience, there’s a trade-off to be had between data model
complexity and storage size. Unless you are pasting the Magna Carta in
each description field or you have two dozen sizes of each show, the
repetition is not going to be an appreciable drag on your system.
Having to calculate a JOIN just to show a record is going to be a
measurable hit on performance every time someone visits a page. It’s
something you will have to test, but my sense is that if you design a
usable admin interface (effectively hiding the repetition from
yourself) you can benefit from the database server not having to do
something like this on every single hit:
SELECT shoes.* photos.* sizes.* whatever.* FROM shoes, photos, sizes,
whatever WHERE 1 AND shoes.sku = photos.sku AND shoes.sku = sizes.sku
AND shoes.sku = whatever.sku ORDER BY sizes.size ASC, sizes.width
ASC …
when you could just
SELECT shoes.* from shoes WHERE 1 ORDER BY size ASC, width ASC
muuuuuch faster.
Walter
dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options