List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:May 8 1999 7:59pm
Subject:Re: syntax/sql question
View as plain text  
If the order_id and package_id are always the same thing,  there is no
need to have separate fields in the database. Now my non-computer
related life experience tells me that it would make sense for a store to
have a set of specially priced packages each including a set of
products, and orders could be placed either for a package or for
separate products. If that is the case, a reasonable way to implement
this is to treat each product as a package consisting of one product.
Thus each order consists of a package. Then you would  generate the
order_id with auto_increment by inserting the selected package_id into
custorder, and you are done. package_id will be determined by user
input. You will either have the user select an already existing package,
and if the one he wants does not exist, he will have to combine the
already existing packages ( possibly invidual items, that are treated by
your program as packages) to come up with a solution. I hope this makes
sense, and I hope I understood well enough what you are trying to
accomplish.

> Well I guess... simply put :-)... my original problem is how do I
> relate the order with the product package where the package is an ever
> changing combination of products and also the product package doesn't
> exist before the order .  One order could include a package
> consisting of ( hammer, screwdriver, wrench) another order could be
> ( hammer, allen_key).  So, in my wisdom i decided instead of making the
> custorder.package_id field type enum and actually storing each item in
> custorder.package_id field, I'll just use the custorder.package_id field
> to reference the product_package table.  Now, since each "package" relates
> only to one order, and doesn't exist otherwise, I have to create the
> package at the same time I create the order and also give it the same
> ID as the order... thus my problem :-).
> 
> ...
> 
> Here is what a single order(23) may look like:
> 
> custorder:
> +----------+------------+
> | order_id | package_id |
> +----------+------------+
> |       23 |         23 |
> +----------+------------+
> 
> product_package:
> +------------+------------+
> | package_id | product_id |
> +------------+------------+
> |         23 |          1 |
> |         23 |          4 |
> +------------+------------+
> 
> ... where the product_package.product_id is from the product table
> below:
> 
> product:
> +------------+--------------+
> | product_id | product_name |
> +------------+--------------+
> |          1 | hammer       |
> |          2 | screwdriver  |
> |          3 | wrench       |
> |          4 | allen key    |
> +------------+--------------+
> 
> Any ideas on the appropriate insert statement for this?  Please take
> a look at my *attempt/mock* statement in my original post above.
> 
> Thanks in advance.
> -Sam
> 
> ----------------------------------------------------------------
> Get your free email from AltaVista at http://altavista.iname.com
> 
> ---------------------------------------------------------------------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail mysql-thread3061@stripped
> 
> To unsubscribe, send a message to the address shown in the
> List-Unsubscribe header of this message. If you cannot see it,
> e-mail mysql-unsubscribe@stripped instead.

-- 
Sasha Pachev
http://www.sashanet.com
Thread
syntax/sql questionsam16008 May
  • Re: syntax/sql questionSasha Pachev8 May
  • Re: syntax/sql questionMike Machado8 May
Re: syntax/sql questionsam16008 May
  • Re: syntax/sql questionSasha Pachev8 May
  • Re: syntax/sql questionJules Bean11 May