Results 1 to 3 of 3

Thread: Database design

  1. #1
    Join Date
    Apr 2012
    Posts
    7

    Database design

    Hi. I need a good database design for a product catalog.

    I need this to store pricing information. When a customer order a product, I have to find the price of that product according to the options chosen.

    Each of my products can have options... and each of those options can have options.. etc.

    Below you can see a part of the options structure.

    First I was thinking I can give each option a price and then calculate the total. But I can not use that approach.
    It is actually not me that decides the prices, I get them from a third party.
    They could of cause give me a price for each option.
    But... Lets say
    binding -> side -> top = 1 dollar.
    binding -> type -> coil binding = 1 dollar.

    So if a user chooses to have those two options the price = 2 dollars...
    But, it could be that the production of these two options together would be more costly cause of some circumstances.
    binding -> side -> top + binding -> type -> coil binding = 3 dollar.

    I could create one table with all combinations, and have a total price for each of those combinations. What you see below is only a part of the options, there are many more. So this table would contain many columns, and that would also kind of be impossible cause the number of records.

    Some of the options will also exclude each other, but that is not up to my system to handle that.

    I guess I am not the first with a problem like this.
    I use Postgres 9.2 and Java.



    art delivery
    preflight required
    trapping required
    binding

    side
    top
    bottom
    right
    left
    type
    channel binding
    coil binding
    corner stitch
    edge gluing (padded)
    hard cover
    loose binding
    plastic comb
    ring
    saddle stitch
    side stitch
    soft cover (perfect binding)
    tape (inexpensive version of the soft cover)
    strip bind
    wire comb
    cover substrate
    color/ink

    color standard
    CMYK
    FIRST
    GRACOL
    Hexachrome
    HIFI
    JapanColor2001
    Monochrome
    SNAP
    SWOP
    coatings (full)
    aqueous
    dull varnish
    gloss varnish
    satin varnish
    silicone
    UV
    full coated surfaces
    spot coated surfaces
    spot colors

  2. #2
    Join Date
    Jul 2012
    Posts
    40

    Check out this Data Model

    Hi

    I have a number of Product-related Data Models on my Database Answers Web Site.
    This looks like the most suitable for you because it combines a flexible product structure with a Price for a Product :-
    Complex Product Catalogs Data Model

    HTH

    Barry

  3. #3
    Join Date
    Apr 2012
    Posts
    7
    I ended up with this design... so far. ( see attachment )
    Attached Thumbnails Attached Thumbnails ER.png  

Posting Permissions

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