Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2006
    Posts
    1

    products and properties

    Hello. I'm trying to create a database for a sweater company and I can't quite realize how to store the products in the database. Each sweater can be produced in several sizes like XS, S, M, L and XL but not necessarily all at once (some modl may only be available in the bigger sizes). Also, each sweater model can be made, in each of its sizes, with many different colours. This means that, for example, sweater model xyz size XS is produced in red and blue, but xyz XL can only be found in green colour. Finally, there's the stock information ('high', low' or 'by request').

    The two ideas I came up with are very rudimentary and inelegant. One is to have a table listing one sweater model in each row and store an array in one column going something like this: XS,#FFC,#234,S,#FFF,#CCC,#111,M,#F0F,#000......... .........
    The other is to create a table listing all the combined options i talked about in the beggining. Of course, that would create a huge monstruous table, I'd guess with more than 2500 rows. Since it is a medium company, I don't expect huge traffic, but I should especulate with the performance of my hosting's computers.

    Well, that's it. I hope this question isn't terribly inadecuate by this forum0s standard.

    Thanks in advance!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Honestly, a 2500-row table is not "monstrous", it is "pretty small". And that is the way to go with tables something like these:
    Code:
    MODEL
    -----
    model_no  name
    --------  ----
    123       Snuggly
    236       Itchy
    ...
    
    
    PRODUCT
    -----------------
    product_code  model_no  colour  size_code
    ------------  --------  ------  ---------
    4001          123       Red     S
    4002          123       Red     M
    4003          123       Red     L
    4004          123       Green   XL
    ...
    (Colour and size may also be tables, though size could just be a column constraint.)

    Imagine how much easier it will be to answer questions like "what products to we have that are Red and available in extra large?" using this design rather than the ugly array column design.

  3. #3
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    Ponzonik

    Your example is a array filing system, not a database, you will have trouble with (a) keys and (b) finding records. Try this ... Sweater.pdf

    In order to arrive at the single table you need (ModelSizeColour), you need the parent tables setup. Either design will do, I prefer the one on the right, because it implements you 'business rules' more exactly (assuming your description is exact). Use Identifying keys and keep things simple.

    Cheers
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

Posting Permissions

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