Re: Cardinality "highly unusual"

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 Received on Wed Feb 16 2005 - 08:35:24 CET