Entity Relationship Diagram - database design problem

Entity Relationship Diagram - database design problem

Post by Sola » Mon, 07 Jul 2003 15:46:19



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!

 
 
 

Entity Relationship Diagram - database design problem

Post by David Porta » Mon, 07 Jul 2003 20:36:20


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 analysing 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, heres 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
--

 
 
 

Entity Relationship Diagram - database design problem

Post by Sola » Mon, 07 Jul 2003 23:22:43


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.


Quote:> 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 analysing 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
> --

 
 
 

Entity Relationship Diagram - database design problem

Post by David Porta » Tue, 08 Jul 2003 00:02:32


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:

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

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

 
 
 

Entity Relationship Diagram - database design problem

Post by Sola » Tue, 08 Jul 2003 08:31:14


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


Quote:> 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:

> http://www.intelligententerprise.com/001020/celko1_1.shtml
> http://www.dbazine.com/tropashko4.html

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

 
 
 

1. Looking for books - entity-relationship diagramming and design

One of my customers is looking for a book on diagramming and design of
entity-relationships.  Would you please let me know more about this topic
and what books you feel would be helpful for him.

Thanks

_______________________________________
Check out the place for technology books - - -
Including Cisco, Unix, CCIE, ATM, IP Design, and
Much, Much More
http://www.technologybooks.com
_______________________________________

2. ORA-12545 under Win2kPro

3. MS Northwind database Entity relationship diagram,

4. Strange Error Message

5. Anyone knows good software for database design like Entity Relationship - Free is most preferable :)

6. xp_cmdshell not seeing newly created folder on network drive

7. Entity-Relationship Diagrams

8. Book suggestions please...

9. Entity Relationship diagrams (IDEF1X)

10. Entity Relationship Diagram from a Normalised Form

11. HELP NEEDED - ENTITY RELATIONSHIP DIAGRAMS

12. Product to make entity-relationship diagrams?

13. Free entity relationship diagram tool