Professional Web Applications Themes

Entity Relationship Diagram - database design problem - Microsoft SQL / MS SQL Server

Hi, Thanking you in advance for your time and effort. Here's the high-level view: 3 main categories: Electronics, Entertainment and Sports. Electronic has 2 sub-categories: Walkman and MP3. Entertainment has 2 sub-categories: DVD and VHS. Sports has 2 sub-categories: Soccer and Basketball. And then each and every sub-category (i.e. Walkman, Soccer, etc.) has 5 items. My question is in regards to creating an ERD. I want the top-most category to be PRODUCTS (this table will link to ORDERDETAILS). I am confused about how my database design should be for the entities under PRODUCTS (i.e. the 3 main categories and its ...

  1. #1

    Default Entity Relationship Diagram - database design problem

    Hi,

    Thanking you in advance for your time and effort.

    Here's the high-level view:

    3 main categories: Electronics, Entertainment and Sports.

    Electronic has 2 sub-categories: Walkman and MP3.
    Entertainment has 2 sub-categories: DVD and VHS.
    Sports has 2 sub-categories: Soccer and Basketball.

    And then each and every sub-category (i.e. Walkman, Soccer, etc.) has 5
    items.

    My question is in regards to creating an ERD. I want the top-most category
    to be PRODUCTS (this table will link to ORDERDETAILS). I am confused about
    how my database design should be for the entities under PRODUCTS (i.e. the 3
    main categories and its respective 2 sub-categories with its 5 items).

    Cheers!


    Solar Guest

  2. #2

    Default Re: Entity Relationship Diagram - database design problem

    From your description I understand that you need to represent products that
    may have different attributes depending on the product type. This is a
    common problem in database design and the decision on whether these types
    merit their own tables can only be made after ysing all the requirements
    of your particular situation. If you have only a few attributes that don't
    apply to all products then it may be better to create these as NULLable
    columns in your Product table.

    Assuming that you have decided to represent the product types a separate
    entities, here’s how you can model them in SQL:


    CREATE TABLE Products (productid INTEGER PRIMARY KEY, producttype CHAR(2)
    NOT NULL CHECK (producttype IN ('EL','EN','SP')), productname VARCHAR(40)
    NOT NULL UNIQUE, UNIQUE (productid,producttype))

    CREATE TABLE ElectronicProducts (productid INTEGER PRIMARY KEY, producttype
    CHAR(2) NOT NULL CHECK (producttype='EL'), FOREIGN KEY (productid,
    producttype) REFERENCES Products (productid, producttype))

    CREATE TABLE EntertainmentProducts (productid INTEGER PRIMARY KEY,
    producttype CHAR(2) NOT NULL CHECK (producttype='EN'), medium CHAR(3) NOT
    NULL CHECK (medium IN ('DVD','VHS','CD')), FOREIGN KEY (productid,
    producttype) REFERENCES Products (productid, producttype))

    CREATE TABLE SportsProducts (productid INTEGER PRIMARY KEY, producttype
    CHAR(2) NOT NULL CHECK (producttype='SP'), FOREIGN KEY (productid,
    producttype) REFERENCES Products (productid, producttype))

    INSERT INTO Products VALUES (1,'EL','Walkman')
    INSERT INTO Products VALUES (2,'EN','The Matrix')
    INSERT INTO ElectronicProducts VALUES (1,'EL')
    INSERT INTO EntertainmentProducts VALUES (2,'EN','DVD')


    The foreign keys and constraints ensure that each product represented in
    Products appears in only one other sub-category table. Attributes common to
    all categories (such as description or price) can go in the Products table.

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



    David Portas Guest

  3. #3

    Default Re: Entity Relationship Diagram - database design problem

    Hi,

    Can each lowest level entity category be in its own table (i.e. Walkman,
    Soccer, etc.) ?
    Because in the future, I may have a lot of products listed in here.

    Thx.

    "David Portas" <REMOVE_BEFORE_REPLYING_dportasacm.org> wrote in message
    news:eKikgK7QDHA.712TK2MSFTNGP12.phx.gbl...
    > From your description I understand that you need to represent products
    that
    > may have different attributes depending on the product type. This is a
    > common problem in database design and the decision on whether these types
    > merit their own tables can only be made after ysing all the
    requirements
    > of your particular situation. If you have only a few attributes that don't
    > apply to all products then it may be better to create these as NULLable
    > columns in your Product table.
    >
    > Assuming that you have decided to represent the product types a separate
    > entities, here's how you can model them in SQL:
    >
    >
    > CREATE TABLE Products (productid INTEGER PRIMARY KEY, producttype CHAR(2)
    > NOT NULL CHECK (producttype IN ('EL','EN','SP')), productname VARCHAR(40)
    > NOT NULL UNIQUE, UNIQUE (productid,producttype))
    >
    > CREATE TABLE ElectronicProducts (productid INTEGER PRIMARY KEY,
    producttype
    > CHAR(2) NOT NULL CHECK (producttype='EL'), FOREIGN KEY (productid,
    > producttype) REFERENCES Products (productid, producttype))
    >
    > CREATE TABLE EntertainmentProducts (productid INTEGER PRIMARY KEY,
    > producttype CHAR(2) NOT NULL CHECK (producttype='EN'), medium CHAR(3) NOT
    > NULL CHECK (medium IN ('DVD','VHS','CD')), FOREIGN KEY (productid,
    > producttype) REFERENCES Products (productid, producttype))
    >
    > CREATE TABLE SportsProducts (productid INTEGER PRIMARY KEY, producttype
    > CHAR(2) NOT NULL CHECK (producttype='SP'), FOREIGN KEY (productid,
    > producttype) REFERENCES Products (productid, producttype))
    >
    > INSERT INTO Products VALUES (1,'EL','Walkman')
    > INSERT INTO Products VALUES (2,'EN','The Matrix')
    > INSERT INTO ElectronicProducts VALUES (1,'EL')
    > INSERT INTO EntertainmentProducts VALUES (2,'EN','DVD')
    >
    >
    > The foreign keys and constraints ensure that each product represented in
    > Products appears in only one other sub-category table. Attributes common
    to
    > all categories (such as description or price) can go in the Products
    table.
    >
    > --
    > David Portas
    > ------------
    > Please reply only to the newsgroup
    > --

    Solar Guest

  4. #4

    Default Re: Entity Relationship Diagram - database design problem

    In principle you can have as many foreign key "levels" as you require. But I
    would question the validity of a "Walkman" table in a product database.

    Maybe what you are really attempting is a simple hierarchy model in which
    case you don't need to create a separate table for each level. Here are some
    useful references:

    [url]http://www.intelligententerprise.com/001020/celko1_1.shtml[/url]
    [url]http://www.dbazine.com/tropashko4.html[/url]

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



    David Portas Guest

  5. #5

    Default Re: Entity Relationship Diagram - database design problem

    Thank you David for your help.
    Appreciate it. =)

    "David Portas" <REMOVE_BEFORE_REPLYING_dportasacm.org> wrote in message
    news:e3bru98QDHA.2424tk2msftngp13.phx.gbl...
    > In principle you can have as many foreign key "levels" as you require. But
    I
    > would question the validity of a "Walkman" table in a product database.
    >
    > Maybe what you are really attempting is a simple hierarchy model in which
    > case you don't need to create a separate table for each level. Here are
    some
    > useful references:
    >
    > [url]http://www.intelligententerprise.com/001020/celko1_1.shtml[/url]
    > [url]http://www.dbazine.com/tropashko4.html[/url]
    >
    > --
    > David Portas
    > ------------
    > Please reply only to the newsgroup

    Solar 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. One-to-many relationship in asp 3.0 object design
    By Jon Maz in forum ASP Database
    Replies: 7
    Last Post: July 1st, 10:05 AM
  3. Replies: 2
    Last Post: August 12th, 04:38 PM
  4. Database Design Problem
    By Aaron in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 8th, 12:36 AM
  5. Trust Relationship Problem
    By Carlos Walker in forum Windows Networking
    Replies: 0
    Last Post: July 4th, 10:35 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