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

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

Brian --

"sys_procedures" is a table which contains the actual SQL queries ...

CREATE TABLE sys_procedures (
   id int(11) NOT NULL auto_increment,
   name varchar(20) NOT NULL default '',
   description varchar(40) default NULL,
   sql text,
   dt_create datetime NOT NULL default '0000-00-00 00:00:00',
   dt_modify timestamp(14) NOT NULL,
   argc int(2) unsigned NOT NULL default '0',
   PRIMARY KEY  (id),
   KEY name (name,dt_create,dt_modify)
) TYPE=MyISAM;

We have functions in the DB class we include in phpLib which read the SQL 
from this table, substitute an array of variables for the "argc" 
placeholders within the SQL (e.g. "{FORMAT}" and "{TYPE}") and then execute 
the resulting query.  Now the PHP contains only the following 3 lines, not 
37 lines of complex SQL:

         $query = $db->proc("get_2_specials");
         $argv = array(FORMAT=>"$word",TYPE=>"special");
         $db->execproc($argv);

I'd be glad to share details and code if you're interested ...

IMHO, this methodology is a whole lot more useful tool, to me, than even 
TEMPLATES ... I don't mind mixing PHP and HTML (that's sorta the way my 
mind works anyway), but once a complex query is debugged, I want it out of 
there - plus, the query is available this way to multiple scripts without 
"cut-and-paste-and-debug" all over again every time it's re-used ... Once I 
optimize a query which searches 400,000 records normalized across a 
half-dozen or more tables, I want to use it for CDs, DVDs and VHSs, not 
copy the query, edit it for the new  department, and debug it again and 
again and again ...

My SQL and PHP abilities are "practical" vs "theoretical", so the concept 
of sys_procedures was a real eye-opener for me about 3 years ago ... with 
deepest thanks to Cheng-Wei Cheng for teaching me this and many other things.

Bob.

At 10:16 AM 11/8/2001 -0600, you wrote:
>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