Professional Web Applications Themes

Database Design Problem - Microsoft SQL / MS SQL Server

I've got a problem I need to solve where I have an item that can have one or more prices, but the item can be bought from one or more distributors and the price we pay is dependent on the distributor. How can I model this sort of relationship where the price for an item is dependent on the distributor. The primary reason why I am doing this is so that I can write logic that will allow me to choose to get the item from the distributor that has the cheapest price. regards, Aaron...

  1. #1

    Default Database Design Problem

    I've got a problem I need to solve where I have an item that can have
    one or more prices, but the item can be bought from one or more
    distributors and the price we pay is dependent on the distributor. How
    can I model this sort of relationship where the price for an item is
    dependent on the distributor. The primary reason why I am doing this is
    so that I can write logic that will allow me to choose to get the item
    from the distributor that has the cheapest price.

    regards,

    Aaron

    Aaron Guest

  2. #2

    Default Re: Database Design Problem

    CREATE TABLE Products (prodid INTEGER PRIMARY KEY, prodname VARCHAR(40) NOT
    NULL UNIQUE)

    CREATE TABLE Distributors (distid INTEGER PRIMARY KEY, distname VARCHAR(40)
    NOT NULL UNIQUE)

    CREATE TABLE ProductDistributors (prodid INTEGER NOT NULL REFERENCES
    Products (prodid), distid INTEGER NOT NULL REFERENCES Distributors (distid),
    price DECIMAL(10,2) NOT NULL CHECK (price>0), PRIMARY KEY (prodid,distid))

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Portas Guest

  3. #3

    Default Re: Database Design Problem

    Hello David,

    Thanks for the reply. This is more or less what I was thinking but wasn't sure.

    Aaron

    David Portas wrote:
    > CREATE TABLE Products (prodid INTEGER PRIMARY KEY, prodname VARCHAR(40) NOT
    > NULL UNIQUE)
    >
    > CREATE TABLE Distributors (distid INTEGER PRIMARY KEY, distname VARCHAR(40)
    > NOT NULL UNIQUE)
    >
    > CREATE TABLE ProductDistributors (prodid INTEGER NOT NULL REFERENCES
    > Products (prodid), distid INTEGER NOT NULL REFERENCES Distributors (distid),
    > price DECIMAL(10,2) NOT NULL CHECK (price>0), PRIMARY KEY (prodid,distid))
    >
    > --
    > David Portas
    > ------------
    > Please reply only to the newsgroup
    > --
    Aaron Guest

Similar Threads

  1. database design and query problem
    By Adnan Barakat in forum MySQL
    Replies: 9
    Last Post: December 30th, 05:07 PM
  2. Database Design Question !!
    By Adam Knight in forum ASP Database
    Replies: 3
    Last Post: August 14th, 08:45 AM
  3. Database Design
    By Mohan in forum Oracle Server
    Replies: 10
    Last Post: July 15th, 10:05 PM
  4. Database design confusion
    By William Wang[MSFT] in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 10th, 07:45 AM
  5. Entity Relationship Diagram - database design problem
    By Solar in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 6th, 11:31 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139