Re: FN-FORUM: Wednesday plea for help with MySQL

date posted 17th January 2007 10:01

Hi Duncan.

I've read your email a couple of times and can't spot the question. What MySQL
help do you need?

Regards
Richard

On Wednesday 17 January 2007 10:37, Duncan Glendinning wrote:
> Good Morning.
> Right, I have asked before and some of you on here have been really
> good to me, so I am hoping you will be able to bail me out again. I am
> in over my head with this, and although to someone who knows what they
> are doing this shouldn't take too long, I am prepared to pay if this
> goes beyond the realms of a "favour".
> I am building an online shop, which has been easy peasy, and have
> built some before. the problem is that other than your average
> products, the client now has kits, which you can build yourself,
> adding a specified number of items from each of 3 lists to make up the
> correct quantity. I have tried to explain the db structure as best I
> could, so if anyone is feeling like being really generous this
> morning, I would love to hear from you!.
>
> Duncan
>
> First off, let me "introduce" my database structure, and for each
> table the important fields we are likely to be needing.
>
>
> "Products"
> productid - primary key
>
>
> "prodVariants"
> variantid
> productid
>
> So, relationship between product to product variant is one to many.
> The "prodVariants" table exists because a product (say some wine, for
> sake of example!) can come in different quantities/variants (such as
> 75cl, 2L, 3L, 10L)
>
>
> "Kits"
> kitid - primary key
> kitAnum
> kitBnum
> kitCnum
>
> "KitVariants"
> kitvarid - primary
> kitid
>
> "KitLists"
> listid - primary
> listname
>
> "listVariants"
> listid
> variantid
>
> So, relationship between "kits" and "kitVariants" is one to many. Kits
> is where you will get a box, and be able to build your product, by
> selecting a number kitAnum of items from List A, kitBnum of items from
> List B, kitCnum of items from List C.
> "KitLists" simply stores the listid, and list name... and the system
> that knows to match the number of items kitAnum with the listname
> called List A from "KitLists" is hard coded as there will only ever be
> 3 lists.(clumsy I know, couldn't see another way of doing this).
> Finally, the relationship between "KitLists" and "listVariants" is one
> to many. Here this table stores the variantids which are allowed in
> each of the lists.
>
> What I am trying to achieve is when the visitor to the site selects a
> kit (by picking a KitVariant), a page opens, which for each of the 3
> lists has two boxes with an add and remove between each. In one box,
> all the variants that are assigned as "allowed" for that list will be
> listed as possibles. Selecting one and clicking on the add will move
> it across to the selected box. Clicking remove will obviously remove
> it. A counter will also keep track of how many items from each list
> have been added, and not allow more than the kitAnum, kitBnum and
> kitCnum specified for the kit to which the kitvariant they selected
> belongs to.
> The end result will be the 3 lists of variants selected from each to
> form the kit.
>
> I hope this will make sense to someone!
>
> Duncan

--
Artumi Systems, 58 Salmon Street, Sheffield, S11 8DD.
Tel 0114 250 7654, Web http://www.artumi.com