Results 1 to 15 of 15
  1. #1
    Join Date
    Jun 2007
    Posts
    22

    Unanswered: Product sizes and prices

    Hi,

    i am developing a clothing order management database.

    I have so far got [order details], [products], [product details] entities.
    This is so that products with the same model code can have a selection of colours and sizes which are held in the product details table.

    I followed this website which was very helpful: http://www.princeton.edu/~rcurtis/ul...database2.html

    The question is now: each size and colour will have a different stock quantity. How do i model this, for example, so when a Small Red t-shirt is added, the small red t-shirts quantity decreases rather than the overall quantity for the model.

    any ideas?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sure, just update the quantity column in the product details table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2007
    Posts
    22
    you mean have the product details like this:

    [productdetailsid] [productid] [colour] [size] [quantity]

    [productdetails1] [productid1] [red] [small] [100]
    [productdetails2] [productid1] [red] [medium] [150]
    [productdetails3] [productid1] [red] [large] [100]
    [productdetails4] [productid1] [blue] [small] [75]
    [productdetails5] [productid1] [blue] [medium] [100]
    [productdetails6] [productid1] [blue] [large] [50]

    likethis?

    thanks for your time

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if that's the way you're doing it, then yes, that's where you'd put the quantity

    "I have so far got [order details], [products], [product details] entities"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2007
    Posts
    22
    is there a better way, its taken me a while to figure this out, the size andcolour issue is preventing me from progressing. I want to stay clear of having to enter 1000's of records, so if i can keep one product code then select the size and colour when a client places an order then that would save shed loads of data inputting

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what you have is fine
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    As I perceive it your products table is actually a misnomer. because you have a range of sizes and a range of styles for each garment then defacto each garment in each size in each colourway is a separate product.

    you may be able to wing it and describe a quantity per size in the product table
    eg
    ProdID
    ProdDesc
    SizeType
    QtySize1
    QtySize2
    ......
    QtySizeSizeN

    but you wil not be abel to ahndle the colourway combinations that way.

    effectively you are building a matrix of colours and sizes for each garment. each interesection on that matrix is a separate product
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jun 2007
    Posts
    22
    hey, thanks for the reply.

    I dont quite understand it though, are you saying i cant do it as i first said? Am i going to have to model every item individually?

    sorry for being stupid!

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes you can do it as you first said -- [order details], [products], [product details] entities

    healdem was just commenting that your [product details] entity was actually your products entity, so your [products] entity was mis-named

    however, once you add attributes to your entitites, it should be immediately obvious that your [products] entity is for shirts while your [product details] entity is for red size 4 shirts, red size 6 shirts, red size 8 shirts, blue size 4 shirts, blue size 6 shirts, et cetera
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2007
    Posts
    22
    What sort of relationship do i need between [products] and [productdetails] also what do i use to link these as primary keys cant have duplicates?

    thanks

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the relationship between products and productdetails is one-to-many

    products has a primary key, which is a foreign key within productdetails

    primary key for productdetails would hopefully be the EAN code

    we;ve had this discussion before, haven't we?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jun 2007
    Posts
    22
    Yeah probably. I had a few days away from it.

    Im going mad trying to get my head round this whole thing. I keep going round in circles. Is there any chance at all that i could send you over my access database and you could have a look, then maybe see what i am doing and tell me where i need to go with it.

    I may have taken on too much, but i am determined to get round it.

    cheers

    j
    Attached Files Attached Files

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nope, that's a no-go option, for me anyway, unless you can make an access 97 MDB file out of it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jun 2007
    Posts
    22
    any ideas?
    Attached Thumbnails Attached Thumbnails entityrelationships.jpg   accessrelationships.jpg  

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you have quantity in the products table, when in fact it belongs in productdetails

    actually, it should be productdetails that is related to orderdetails

    the shirt that you can hold in your hand has a size and a colour, that's an example of a productdetails row

    the line of shirts with the roll collar and only four buttons, that comes in seven sizes and eight colours, that's an example of the products row

    you don't order a dozen of the shirts, you order a dozen of the red shirts in size 12

    see the difference?

    but you know this, don't you? i mean, it was your idea in the first place
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •