Cardinality "highly unusual"

Hugo Kornelis wrote:
....
 > Based on your description, you've chosen the right cardinality. I don't
 > know if the statement of MS Visio EA about this type of cardinality being
 > rare is true or not. In any case, I wouldn't worry about it. It doesn't
 > sound like an error message - more like a warning. Consider it a reminder:
 > the program tells you "this is unusual - you might want to double-check if
 > you didn't make an error here".

Hi Hugo

Thanks, you've helped to set my mind at rest!

 > Is the text you're working from specifically written for your situation
 > (rare books, that are unique and can therefor be sold only once). I'm
 > asking because the solution from the book is exactly what a reular book
 > store would use (though I'd personally prefer to call the table "Titles"
 > instead of "Books" in a regular book store). The BookOrder table is used
 > to store the many-to-many relationship between Order and Book (title),
 > since in a regular book, each order has one or more books and each title
 > can be sold in zero or more orders.

Right, you would use a composite entity to resolve a n:m relationship. That
makes sense if the bookshop sells new books. But my text (actually an MS
book) deals explicitly with rare and unique books, and uses a composite
entity to resolve a 1:m relationship between order and book. This strikes me
as logically and conceptually wrong, but if it is the preferred way of doing
things then so be it.



Try as I might, I simply cannot generate this sort of schema from an ORM
source model (my preferred modeling method). Instead, I have to do a lot of
tweaking of the logical diagram mapped from the ORM source model.



Matt<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: Cardinality &quot;highly unusual&quot; 

Matt M wrote:
 > Hugo Kornelis wrote:
 > ...
  >> Based on your description, you've chosen the right cardinality. I don't
  >> know if the statement of MS Visio EA about this type of cardinality being
  >> rare is true or not. In any case, I wouldn't worry about it. It doesn't
  >> sound like an error message - more like a warning. Consider it a
  >> reminder:
  >> the program tells you "this is unusual - you might want to double-check
  >> if
  >> you didn't make an error here".
 >
 > Hi Hugo
 >
 > Thanks, you've helped to set my mind at rest!
 >
  >> Is the text you're working from specifically written for your situation
  >> (rare books, that are unique and can therefor be sold only once). I'm
  >> asking because the solution from the book is exactly what a reular book
  >> store would use (though I'd personally prefer to call the table "Titles"
  >> instead of "Books" in a regular book store). The BookOrder table is used
  >> to store the many-to-many relationship between Order and Book (title),
  >> since in a regular book, each order has one or more books and each title
  >> can be sold in zero or more orders.
 >
 > Right, you would use a composite entity to resolve a n:m relationship.
 > That > makes sense if the bookshop sells new books. But my text (actually
 > an MS book) deals explicitly with rare and unique books, and uses a
 > composite entity to resolve a 1:m relationship between order and book.
 > This strikes me as logically and conceptually wrong, but if it is the
 > preferred way of doing things then so be it.
 >
 > Try as I might, I simply cannot generate this sort of schema from an ORM
 > source model (my preferred modeling method). Instead, I have to do a lot
 > of > tweaking of the logical diagram mapped from the ORM source model.

I clicked on the send button too hastily. Please ignore the bit about ORM at
the bottom.

Thanks for your help

Matt<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: Cardinality &quot;highly unusual&quot; 

On Tue, 15 Feb 2005 19:33:36 +0700, Matt M wrote:

 >Thanks, you've helped to set my mind at rest!

Hi Matt,

You're welcome!


 >Right, you would use a composite entity to resolve a n:m relationship. That
 >makes sense if the bookshop sells new books. But my text (actually an MS
 >book) deals explicitly with rare and unique books, and uses a composite
 >entity to resolve a 1:m relationship between order and book. This strikes me
 >as logically and conceptually wrong, but if it is the preferred way of doing
 >things then so be it.

I don't think it's the preferred way. I would definitely use 2 tables,
with the Books table holding a NULLable column with FOREIGN KEY to the
Orders table.

However, there are people who prefer to put all attributes in one table
and use seperate tables for all relationships. That would result in three
tables: the Books table and Orders table you already have, plus one
BookSales table. This one should have FOREIGN KEY constraints to Books and
to Orders, but a PRIMARY KEY on only the column that forms the FOREIGN KEY
to Books - making this a one to zero or one relationship (aka subtype). If
this is the design used in your book, then it's obviously written by
someone who prefers to keep relationships seperate from attributes. But if
the design in your book includes both the BookID and the OrderID in the
PRIMARY KEY for BookSales, then the author has simply made a mistake, as
that design would allow you to sell the same book in more than one order.


 > Try as I might, I simply cannot generate this sort of schema from an ORM
 >source model (my preferred modeling method). Instead, I have to do a lot of
 >tweaking of the logical diagram mapped from the ORM source model.

I don't have Visio EA, but I did play around a bit with VisioModeler to
see what I could find. The "straightforward" approach that I actually
prefer (yielding just 2 tables, with the 'Zero-or-One-to-One-or-More'
relationship) went just fine; Visiomodeler does not warn about this
relationship being unusual but just generates the tables.

I managed to force VisioModeler into using three tables using the
following steps:
1. Include "SoldBook" as subtype of "Book";
2. Change "Order consists of Book" to "Order consists of SoldBook";
3. Open the properties for "SoldBook" and select "Map to seperate table"
in the subtype tab.

After these steps, VisioModeler generated three tables.

However, I'd never really do this. The whole point of ORM is that you
start from the customers needs, go on to build a conceptual model from
that, then transform that to a relational model. You can always decide to
manually adapt the relational model, e.g. for better performance, but you
should not try to change the ORM model to get it to generate the adapted
version of the relational model. Just change the DDL and document your
changes, along with the reasons for making them.


 >I clicked on the send button too hastily. Please ignore the bit about ORM at
 >the bottom.

Oops.

Please don't read my reply to the bit I should have ignored <g>

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: Cardinality &quot;highly unusual&quot; 

Hugo Kornelis wrote:
....
 > However, there are people who prefer to put all attributes in one table
 > and use seperate tables for all relationships. That would result in three
 > tables: the Books table and Orders table you already have, plus one
 > BookSales table. This one should have FOREIGN KEY constraints to Books and
 > to Orders, but a PRIMARY KEY on only the column that forms the FOREIGN KEY
 > to Books - making this a one to zero or one relationship (aka subtype). If
 > this is the design used in your book, then it's obviously written by
 > someone who prefers to keep relationships seperate from attributes. But if
 > the design in your book includes both the BookID and the OrderID in the
 > PRIMARY KEY for BookSales, then the author has simply made a mistake, as
 > that design would allow you to sell the same book in more than one order.

The book is MS's training kit for the 70-229 SQL Server exam. The bookshop
schema doesn't explicitly identify the PK of BookOrders, it just shows
BookOrders as having two columns: BookID and OrderID. So, contrary to what I
said earlier, it's possible that the author is using the approach you
describe (though if they are, they don't explain it).

 > I managed to force VisioModeler into using three tables using the
 > following steps:
....
 > However, I'd never really do this. The whole point of ORM is that you
 > start from the customers needs, go on to build a conceptual model from
 > that, then transform that to a relational model. You can always decide to
 > manually adapt the relational model, e.g. for better performance, but you
 > should not try to change the ORM model to get it to generate the adapted
 > version of the relational model. Just change the DDL and document your
 > changes, along with the reasons for making them.

That sounds like excellent advice. Thanks!

Matt<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: Cardinality &quot;highly unusual&quot;