[DB-SIG] Re: [Python-Dev] database APIs

[Luke Kenneth Casson Leighton]

On Wed, Feb 05, 2003 at 06:52:35PM -0500, Kevin Jacobs wrote:
> So how many reports do you have?  What do they do?  And how many lines (on
> average) do they take?  

 14 reports.

 stock control, including purchasing reports (admin only),
 products             (customer / admin modes),
 catalogue management (admin only),
 orders               (admin / customer mode),
 invoices             (admin only),
 login,
 account management/creation (admin only)
 order/purchase creation (customer only)

 lines:

 370 avg.
 180 min.
 1280 max (stock control!  way too big!)
 600 max for everything else.

 
 what else is bugging me.

 oh, yeh.  my product-related SQL tables are:

 - products (descriptions and stock and sizes)

 - product supplier codes (to record supplier codes for same product)

 - prices (many prices per product, to do volume discounting)

 - product_groups (bundling and Packs)

 now, the product_groups thing _was_ a great idea, but i am
 overloading it and it means that a product gets bought as
 one thing (one product id) and then multiplied to another
 thing (another product id).  this isn't okay.


 i want to add in a new table, product_sizes.

 this will contain the size description and the quantity per
 case, per box.

 it means that i can buy 4x5L of product from a supplier, and
 sell it as 5L or 4x5L it doesn't matter which, it's the
 same product_id.


 product_sizes

 prod_id    multipler
 1          4
 1          1

 "oh yeh, this is actually the same product, let's show
  the same stock quantity divided by the multiplier figure,
  rounded down".


 now i have to go through ALL my code, including the libraries,
 because the product "listing" function will no longer be
 a single table job it will be a JOIN between products and
 product_sizes.

 if i had views in mysql i would be more confident.  *sigh*.

 why i mention this is because if i had the right infrastructure
 in place it would be easy.

 as it is i am going to have to create "view" classes which
 actually have the same interface as the old products table
 but underneath do the JOIN for me.

 rename the products table or something.

 all very messy.

 l.