Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > BOOK: Beginning Database Design Solutions ISBN: 978-0-470-38549-4
|
BOOK: Beginning Database Design Solutions ISBN: 978-0-470-38549-4
This is the forum to discuss the Wrox book Beginning Database Design Solutions by Rod Stephens; ISBN: 9780470385494
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning Database Design Solutions ISBN: 978-0-470-38549-4 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 12th, 2011, 07:42 PM
Registered User
 
Join Date: Jan 2011
Posts: 1
Thanks: 0
Thanked 1 Time in 1 Post
Default How to model the Polymorphism Association In the Relational Database

Hi Mr Rod , first of all I really want to say congratulation For your
excellent and very usefull book about database Designning And modelling


I think this book is one of the hottest one that covers many Serious problems.

I really exited when i saw the table of the topics and took a look
at the book,i think you coverd Inheritance very well,but what
about Polymorphism Association And ARC Relashinship.

My Clear Question is :

How We can Model The Polymorphism Association and
ARC Relashinship and What is The Best Pattern For Mapping This Association
To Tables In a Relational Data Base
?


it seems that this topic is missed ?am i right?could you pleas guid me where can i see a full discussion about

this issue?it seems that this issue is coverd in Old books and in the modern books we dont see anything?

could you pleas introduce me some resource about this issue?

best wishes ,and thank you very much.


(oooh ,I Must Say That I love WPF Programmer's Reference TOO ,YES, IT IS really ONE OF THE Best Books )

Last edited by siamak; January 12th, 2011 at 07:59 PM..
The Following User Says Thank You to siamak For This Useful Post:
Rod Stephens (January 14th, 2011)
 
Old January 14th, 2011, 01:34 PM
Rod Stephens's Avatar
Wrox Author
 
Join Date: Jan 2006
Posts: 647
Thanks: 2
Thanked 96 Times in 95 Posts
Default

Thanks for the kind words. Post a review when you have a chance!

Polymorphism

In the code behind the database, this means the ability to treat an object as if it were of another class if it is also of that class. In particular, you can treat an object as if it were of a class from which it inherits. For example, if Employee inherits from Person, then you can treat an Employee as a Person (a strange concept to some managers).

You can model the inheritance by making a Person table with a PersonId key field. Then make an Employee table that has a PersonId field to link back to the corresponding Person.

You could also have Customer and Student classes that inherit from Person and their tables would also use PersonId to link to the Person tables.

So how do you use polymorphism? I think you could do this directly in the database by using the right table and possibly suitable joins. For example, if you want to treat a bunch of objects as Persons, you would query the Person table. If you want to treat Employees as Persons, you would query the Person table but join in the Employees table so you don't get any Customers or Students.

In complicated cases this could get a bit convoluted. It might be easier to understand (for me at least) to map the data to objects in the code (possibly using an ORM) and then use the polymorphism supported by the language. An object-oriented language has better support for polymorphism than a relational database. I haven't used object-relational stores so I don't know if they do a better job.

ARC Relationships

By this I assume you mean to make two relationships exclusive. For example, a Person can be an Employee or Customer but not both.

I'm not sure what the best solution is. I'm not sure there is one best solution that fits all circumstances.

A reasonably straightforward solution is to give the Person table EmployeeId, CustomerId, and StudentId fields, and then use a row-level constraint to ensure that exactly one of those fields is not null.

If you think of the subclasses as similar types of objects, however, then this isn't well normalized because the three ID fields hold the same "kind" of data, i.e. an ID for a subclass object. In that regard, a better design would just let the subclass tables use the PersonId to link back to the parent record (as in the original polymorphism design). Then you'd have to use more complicated cross-table constraints in the subclass tables to ensure exclusivity. For example, the Employee table's constraint would verify that there was no record with the same PersonId in the Customer or Student tables.

I think this is a better design but it's probably not as efficient and adding new subclasses will still be a hassle because you'll have to update all of the other subclass table constraints.

Another solution that I think is common is to give the Person table SubtypeType and SubtypeId fields. The SubtypeType tells what kind of Person this is (Employee, Customer, or Student) and SubtypeId gives the ID in the subtype table.

Another approach would be to declare that this is a business rule (in theory, a Person could be an Employee, Customer, and Student all at the same time) and move the constraint into a middle tier or even the user interface code. This is a bit of a cop out because you might have cases where the exclusivity is physically guaranteed, but it still might be easier to handle the situation this way.

Let me know if you have any thoughts (or if I've missed your questions entirely).
__________________
Rod

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)
 
Old January 14th, 2011, 10:20 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Quote:
Another solution that I think is common is to give the Person table SubtypeType and SubtypeId fields. The SubtypeType tells what kind of Person this is (Employee, Customer, or Student) and SubtypeId gives the ID in the subtype table.
The problem with this is that you can't use the database's built-in referential integrity.

That is, you can't say
Code:
CREATE TABLE foo (
    subtypeType INT, -- or use ENUM if the database supports it!
    subtypeID INT REFERENCES employee(id) OR student(id) OR customer(id), 
    ...
****************

Quote:
A reasonably straightforward solution is to give the Person table EmployeeId, CustomerId, and StudentId fields, and then use a row-level constraint to ensure that exactly one of those fields is not null.
And your objection to that is valid, but I still think it's a better solution. It does allow you to ensure referential integrity:
Code:
CREATE TABLE foo (
    employeeID INT NULL REFERENCES employees(id),
    studentID INT NULL REFERENCES students(id),
    customerID INT NULL REFERENCES customers(id),
    ...
Indeed, I have used both techniques in the past. Both have strengths, both have weaknesses. Overall, I prefer the second, but it's not a clear-cut choice.

Of course, the real solution is to use a true OODBMS instead of an RDBMS. <grin/>

(I used to work for Objectivity...and we'd happily handle polymorphism natively in the database. Also once created a persistent store for Java that did it right.)
 
Old January 15th, 2011, 11:17 AM
Rod Stephens's Avatar
Wrox Author
 
Join Date: Jan 2006
Posts: 647
Thanks: 2
Thanked 96 Times in 95 Posts
Default

I agree. Neither solution is perfect.

The best solution may depend on your application. If the exclusivity is a business rule, it may make the most sense to pull it into the middle tier.
__________________
Rod

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)





Similar Threads
Thread Thread Starter Forum Replies Last Post
non-relational database Michel SQL Server 2000 4 February 21st, 2006 04:05 PM
applying Filters to a relational database ggiibboo Access 3 February 2nd, 2006 02:55 AM
Post/Get from 2 Tables in Relational Database TSEROOGY Classic ASP Databases 1 November 16th, 2004 03:28 AM
Converting Flat File Database to Relational DB rj_655@hotmail.com Access 5 June 12th, 2004 09:33 AM
Indexing XML in relational database - performance cooold XML 1 November 13th, 2003 03:49 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.