Re: Cardinality "highly unusual"

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"

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)