RE: [Phplib-users] OT: Database Design Help

[Brian P. <bp...@ct...>]

I'm fairly fluent with SQL joins, but I've never heard of a sys_procedures
table. Could you explain how that works? How does that remove the joins from
your PHP code?

-----Original Message-----
From: Bob Bowker [mailto:bo...@iN...]
Sent: Thursday, November 08, 2001 10:04 AM
To: Php...@li...
Subject: Re: [Phplib-users] OT: Database Design Help


Hi --

Jesse (and others) focused my attention and helped solve the original issue 
- how to organize the basic data - but I have also addressed this new 
problem of attributes ...

I've got 2500 products, 11 categories, 88 sub-categories, and 24 attributes 
... each product can belong to several sub-categories, and attributes can 
change daily.  I have tried to normalize the data as follows:

attributes         id,attribName,icon,ref_AttribsFieldName
categories         id,catName
subcats    id,subName,ref_catId
prods              prodnum,name,keywords
prods_details      prodnum,price,colors, ... description
prods_attribs      prodnum,attr1,attr2, ... attr24
prods_assign       prodnum,ref_subcatIdCsv

MySQL queries are straightforward and fast ... I'm not happy with the CSV 
list of sub-categories to which a product belongs in the prods_assign table 
- but for now, using MySQL's "like '%xxxx%'" and a few extra lines of PHP 
seems simpler than 88 more fields ...

The one time-saver I stumbled on quite innocently (no deep thought and 
planning here!) is the ref_AttribsFieldName field in table attributes - it 
contains the name of the corresponding field in prods_attribs, which makes 
both the query and the input form variable naming quite simple ...

BTW, all queries get moved into a sys_procedures table as soon as debugging 
is complete, so all those messy JOINs are removed from the PHP as soon as 
we know they're working correctly ...

The design requirements are driven by the fact that we are creating a 
Control Panel page for the client to manage his own database ... 
add/edit/delete products and add/edit categories, sub-categories, 
attributes, etc. etc.  Leaves us to write code, not do data entry at 15% of 
our programming rate!

Bob.

At 05:56 AM 11/8/2001 -0500, you wrote:
>Yes, this is a dilemma.  I am sure there are lots of ways to solve this
>problem and a lot depends on the way the data is being used.  The way you
>suggest is probably the best for speed.  But, I can make a mess of a table
>by having lots of extra columns.
>
>Another way would be to create a table of attributes.  The columns would be
>something like
>
>Product_attributes
>     product_id
>     attr_type
>     attr_value
>
>It makes queries a little harder because you have to join in this table and
>you may want to add other columns to better describe the attribute.  But
>this is how I would do it.
>--
>Jesse
>
>
> > From: Philip Strnad <ph...@st...>
> > Date: Wed, 07 Nov 2001 22:28:54 -0800
> > To: Jesse Swensen <ph...@sw...>
> > Cc: Bob Bowker <bo...@iN...>, 
> "php...@li..."
> > <Php...@li...>
> > Subject: Re: [Phplib-users] OT: Database Design Help
> >
> > How would you go about storing attributes for the products?  Let's say
you
> > have
> > three product types:
> >
> > - books
> > - CDs
> > - electronics
> >
> > Each of these categories could have many attributes, and to make matters
> > worse,
> > each one can have different attributes.  For example, a book would have
an
> > ISBN
> > number, which a CD wouldn't have.  The electronics might have a color
> > attribute,
> > which CDs and books wouldn't have (at least there would be no point in 
> storing
> > this attribute).
> >
> > I've run into this situation a few times and have never found an ideal
> > solution.  The only solution I see is to have lots of attribute columns 
> in the
> > products table and for every product a bunch of these would have null 
> values
> > since certain attributes wouldn't be applicable.
>
>
>
>_______________________________________________
>Phplib-users mailing list
>Php...@li...
>https://lists.sourceforge.net/lists/listinfo/phplib-users


_______________________________________________
Phplib-users mailing list
Php...@li...
https://lists.sourceforge.net/lists/listinfo/phplib-users