Re: UML class diagram -> SQL-DBMS tables

karl wettin <wettin_at_users.sourceforge.net> wrote in message news:<20031006025702.353c70ec.wettin_at_users.sourceforge.net>...
> Group,
>
> I'd like to hear your opinions on my way of representing an UML class
> diagram as SQL-DBMS tables.
>
> <http://snigel.dnsalias.net/snigelwiki/translating_20class_20diagram_20to_20SQL_20tables>
>
>
> This is the content of above Wiki article:
>
> [[TableOfContents]]
>
> = Abstract =
>
> * Each class is represented as a table.
> * Non-multiple attributes are represented as a column in the class table
> * Fixed size multiplicity attributes are represented in their own table
> * Generalized classes share ["PK"] with their super class using an ["SK"].
> * Association relations are represented in their own table, no matter of the multiplicity size.
> * Qualifiers are represented in their own table, since each qualifier is a class.
>
> = SQL table naming convention =
>
> * Class table representation : {{{Classname}}}
> * Multiple attribute tables : {{{Class name_attributeName}}}
> * Association tables : {{{Classname_leftAssociationEndName_rightAssociationEndName_Class}}}
> * Association tables with association class : {{{Classname_leftAssociationEndName_AssociationClass_rightAssociationEndName_Class}}}
> * Qualifier tables : {{{Classname_rightAssociationEndNameQualifier}}}
>
> = Example =
>
> This is supposed to represent a simple webshop, something I hope everybody can relate to. It contains all associations but realizing super relations, and they are not covered in this draft. Realization usually only means ["polymorphic"] methods, and methods aren't persistent.
>
> == UML class diagram ==
>
> The class diagram follows the ["Silvertejp"] ["ASCII"] ["UML"] notation.
>
> {{{
> [WebShop](email:String)--- {0..*} -[Customer(+password:String;+name:String{2..5};)]----[PhysicalAdress(+streetAdress:String{1..5};+zipCode:String;+countryCode:int;)]
>
> [Customer]--- {0..*} -<<>>[ShoppingCart(+created:Date;)]---| {0..*} ->[Product]-- {0..*} {0..*} --[ProductCategory(+title:String;)]<>-- +parent +child {0..*}--[ProductCategory]
> |
> \--[ShoppingCartProductSize(+size:int;)]
>
> [abstract Product(+title:String;+price:float;)]<|-----[Book(+ISBN:String;)]
> }}}
>
> == SQL-DBMS tables ==
>
> This is ["PostgreSQL"]-specific ["SQL"].
>
> {{{
> CREATE TABLE "WebShop"
> (
> "PK" int8 PRIMARY KEY
> );
>
> CREATE TABLE "WebShop_CustomerQualifier"
> (
> "PK" int8 PRIMARY KEY,
> "email" varchar(50)
> );
>
> CREATE TABLE "Customer"
> (
> "PK" int8 PRIMARY KEY,
> "password" varchar(50)
> );
>
> CREATE TABLE "Customer_name"
> (
> "customer_FK" int8 REFERENCES "Customer" ("PK"),
> "_order" int2,
> "name" varchar(50)
> );
>
> CREATE TABLE "PhysicalAdress"
> (
> "PK" int8 PRIMARY KEY,
> "zipCode" varchar(12),
> "countryCode" int2
> );
>
> CREATE TABLE "PhysicalAdress_streetAdress"
> (
> "PysicalAdress_FK" int8 REFERENCES "PhysicalAdress" ("PK"),
> "_order" int2,
> "streetAdress" varchar(50)
> );
>
> CREATE TABLE "ShoppingCart"
> (
> "PK" int8 PRIMARY KEY,
> "created" date
> );
>
> CREATE TABLE "ShoppingCartProductSize"
> (
> "PK" int8 PRIMARY KEY,
> "size" int4
> );
>
> CREATE TABLE "Product"
> (
> "PK" int8 PRIMARY KEY,
> "title" varchar(50),
> "price" float4
> );
>
> CREATE TABLE "ProductCategory"
> (
> "PK" int8 PRIMARY KEY,
> "title" varchar(50)
> );
>
> CREATE TABLE "Book"
> (
> "SK" int8 PRIMARY KEY REFERENCES "Product" ("PK"),
> "ISBN" varchar(50)
> );
>
> CREATE TABLE "WebShop_webShop_customer_Customer"
> (
> "webShop_FK" int8 REFERENCES "WebShop" ("PK"),
> "webShop_CustomerQualifier_FK" int8 REFERENCES "WebShop_CustomerQualifier" ("PK"),
> "customer_FK" int8 REFERENCES "Customer" ("PK")
> );
>
> CREATE TABLE "Customer_customer_physicalAdress_PhysicalAdress"
> (
> "customer_FK" int8 REFERENCES "Customer" ("PK"),
> "physicalAdress_FK" int8 REFERENCES "PhysicalAdress" ("PK")
> );
>
> CREATE TABLE "Product_product_productCategory_ProductCategory"
> (
> "product_FK" int8 REFERENCES "Product" ("PK"),
> "productCategory_FK" int8 REFERENCES "ProductCategory" ("PK")
> );
>
> CREATE TABLE "ProductCategory_parent_child_ProductCategory"
> (
> "parent_FK" int8 REFERENCES "ProductCategory" ("PK"),
> "child_FK" int8 REFERENCES "ProductCategory" ("PK")
> );
>
> CREATE TABLE "ShoppingCart_shoppingCart_ShoppingCartProductSize_product_Product"
> (
> "shoppingCart_FK" int8 REFERENCES "ShoppingCart" ("PK"),
> "ShoppingCartProductSize_FK" int8 REFERENCES "ShoppingCartProductSize" ("PK"),
> "product_FK" int8 REFERENCES "Product" ("PK")
> );
> }}}
>
>
>
> karl

www.odtug.org

There is an article in a recent journal about using object views as a layer on top of your relational database that maps to classes. Its oracle specific, but Id assume that other vendors have similiar implementations.

I do NOT like the idea of mapping classes to tables. Makes the relational model too complex. A middle layer is better.

one thing to be careful of. Most vendors are using CBOs now and heavily 'layered' views tend to have performance issues in a CBO environment.
If you go 5-7 layers deep with views and make them complex. So mapping needs to be cautious and dont over do it. Received on Wed Oct 08 2003 - 15:00:14 CEST