Professional Web Applications Themes

Database Design - Microsoft SQL / MS SQL Server

I guess we should have a composite primary key on IngredientID and ProductID in Ingredients table another composite primary key on VendorID and IngredientsID in Vendors table. This will let us store multiple prodults in which a single ingredient is present as well as to store multiple ingredients supplied by a single vendor.. Sampangi   sample T-SQL script  (ProductID),  (IngredientID),  have many ingredients  sample queries:  p.ProductID = i.ProductID  i.IngredientID = [/ref] and [/ref] to [/ref] and  > > >. >[/ref]...

  1. #1

    Default Re: Database Design


    I guess we should have a composite primary key on
    IngredientID and ProductID in Ingredients table
    another composite primary key on VendorID and
    IngredientsID in Vendors table. This will let us store
    multiple prodults in which a single ingredient is present
    as well as to store multiple ingredients supplied by a
    single vendor..

    Sampangi
     
    sample T-SQL script 
    (ProductID), 
    (IngredientID), 
    have many ingredients 
    sample queries: 
    p.ProductID = i.ProductID 
    i.IngredientID = [/ref]
    and [/ref]
    to [/ref]
    and 
    >
    >
    >.
    >[/ref]
    Sampangi Guest

  2. #2

    Default Re: Database Design

    Do you use UPC or other industry standards for your products? If so, use
    them. Please post DDL, so that people do not have to guess what the
    keys, constraints, Declarative Referential Integrity, datatypes, etc. in
    your schema are. Here is a wild guess at what you have now:

    CREATE TABLE Products
    (product_id INTEGER NOT NULL PRIMARY KEY,
    product_name CHAR(35) NOT NULL,
    ..);

    CREATE TABLE Vendors
    (vendor_id INTEGER NOT NULL PRIMARY KEY,
    vendor_name CHAR(35) NOT NULL,
    ..);

    CREATE TABLE Ingredients
    (ingredient_id INTEGER NOT NULL PRIMARY KEY,
    description VARCHAR(100) NOT NULL,
    ..
    );

    The recipes and the suppliers for the ingredients have no realtionship
    with each other, so you need to show two relationships.

    CREATE TABLE Recipes
    (product_id INTEGER NOT NULL,
    REFERENCES (product_id)
    ON UPDATE CASCADE,
    ingredient_id INTEGER NOT NULL,
    REFERENCES Ingredients(ingredient_id)
    ON UPDATE CASCADE,
    PRIMARY KEY (product_id, ingredient_id));

    CREATE TABLE Suppliers
    (vendor_id INTEGER NOT NULL,
    REFERENCES Vendors (vendor_id)
    ON UPDATE CASCADE,
    ingredients_id INTEGER NOT NULL,
    REFERENCES Ingredients (ingredient_id
    ON UPDATE CASCADE,
    price DECIMAL(12,4) NOT NULL,
    ...,
    PRIMARY KEY (vendor_id, ingredients_id));

    Now you can use a relational division to find a vendor who can be the
    single supplier for a particular poduct, etc.

    --CELKO--


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

Similar Threads

  1. database design and asp question
    By Michael in forum Dreamweaver AppDev
    Replies: 2
    Last Post: June 27th, 09:23 AM
  2. Reusable database design
    By Ron St-Pierre in forum PostgreSQL / PGSQL
    Replies: 0
    Last Post: December 10th, 10:31 PM
  3. Database Design
    By Mohan in forum Oracle Server
    Replies: 10
    Last Post: July 15th, 10:05 PM
  4. Database design help required
    By Lee Bevan in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 1st, 01:34 PM
  5. What should be the Database Design in this case?
    By Puneet Agarwal in forum Oracle Server
    Replies: 3
    Last Post: December 12th, 11:20 AM

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