 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / General DB Topics / DB Theory / March 2004How to code a supertype subtype relationship
Let's talk about supertype-subtype relationship. Here's the problem: I have to use a column in the supertype to find a corresponding subtype. Here's an example: Say we have a supertype, S, with three subtypes, X, Y, and Z. I add a row, R1, to supertype S. I now add a row to subtype Y that corresponds to the row R1 in supertype S. Later, I query row R1 in supertype S. I now have a problem: which subtype corresponds to R1 in S. X, Y, or Z? Well, I have solved this problem by adding a column to S that tells its type in this case Y. So, I look at the type column in R1 and then lookup the child row in subtype Y. Is there a better way to code this? How do other people work with a supertype-subtype relationship? bogston The two most common ways are the one that you are using and adding the columns of subtypes to the supertype table and allowing attributes of X for example to be null for a subtype Y. > Let's talk about supertype-subtype relationship. Here's the problem: > I have to use a column in the supertype to find a corresponding [quoted text clipped - 15 lines] > > bogston > The two most common ways are the one that you are using and adding the > columns of subtypes to the supertype table and allowing attributes of X for > example to be null for a subtype Y. Well, that confirms pretty much what my co-workers and I were thinking. Adding the subtypes to the supertype isn't practical in our situation. There is one supertype with ten subtypes. The smallest subtype has 15 columns. To combine into one table there would be over 200 columns. That's not pretty. Thanks. > > Let's talk about supertype-subtype relationship. Here's the problem: > > I have to use a column in the supertype to find a corresponding [quoted text clipped - 15 lines] > > > > bogston > > The two most common ways are the one that you are using and adding the > > columns of subtypes to the supertype table and allowing attributes of X for [quoted text clipped - 3 lines] > thinking. Adding the subtypes to the supertype isn't practical in our > situation. There is one supertype with ten subtypes. Perhaps the large number of subtypes indicates some flaw in the design? You aren't programming mammal classification, aren't you? (For huge hierarchies type-subtype paradigm breaks anyway, as one better program generic parent-child relationship instead of making them types). > The smallest > subtype has 15 columns. To combine into one table there would be over > 200 columns. That's not pretty. Thanks. Thanks god somebody still is able to smell if the design stinks! Normally, people give it much less thought. You might enjoy googling the thread "Stupidest table I ever saw". > > "Tom Hester" <$$tom@metadata.com> wrote in message > news:<76b19$4060bb79$45033832$26263@msgid.meganewsservers.com>... [quoted text clipped - 11 lines] > type-subtype paradigm breaks anyway, as one better program generic > parent-child relationship instead of making them types). Very good! The data model I am working with is not ideal, but it is correct for the problem at hand. Three people have beat on this data model and have agreed that the design is the best we can do. An existing data model is being converted here. The historical data must be preserved. Compromises must be made. > > The smallest > > subtype has 15 columns. To combine into one table there would be over [quoted text clipped - 3 lines] > people give it much less thought. You might enjoy googling the thread > "Stupidest table I ever saw". Surely that table is not in production anywhere on this planet. > Surely that table is not in production anywhere on this planet. Huh? It a masterpiece of one of the big three DB vendors(!), won't tell you which one in order to avoid flamewars. >An >existing data model is being converted here. The historical data must >be preserved. Compromises must be made. Are you under the impression that a) converting an existing logical model and implementation, and b) preserving historical data *requires* compromises in the new logical model? In the new implementation?  SignatureMike Sherrill Information Management Systems |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.