One to Many Table Relationship

[Arhat]

I'm having trouble modeling a relationship in a database. I need conceptual help. Here is an abstract description: A Class has several common attributes, but there are some attributes that differentiate different instances of this Class. For example, this Class may have x fields. ClassB will have those same fields and several others, so Class B will have (x+y) fields. Class C will also have x fields and an additonal Z fields for a total of (x+z) fields. x and y may be the same number or not, but they will certainly not be the same type of fields. See attached for picture.

My thinking is that since all the classes have x fields in common, why not have super class in its own table (ClassA), and have two (or more) tables composed of y and z fields respectively. The primary key (A1, lets say autonumbered) in the ClassA table would also be the primary key in ClassB and ClassC tables.

My goal is is have a subform within a form, where the form has all the fields from ClassA and the subform will have all the fields from either ClassB or ClassC depending on another field in ClassA (A2, text type for example). In essence, the layout of the subform would change depending on the value of A2 from the ClassA table.

Can this even be done. I think I'm missing something here. Would anyone care to offer advice?

 

[Banana]

Ahhh, I have this same case.

First, you create a table of Class. Then you create a table of attributes about Class. You just need a junction table between the Class and Attributes to tell what Class has what attributes. Now, for an actual instance (e.g. something that belongs to a class), you need yet another junction table; this has to be a three way junction between Class, Attribute, and Instance/Object/whatever it is with a entry for "Answer" (e.g. answers about the object's attributes as defined by class).

On the form, you'd have a datasheet subform that querys the attributes for a given class that object belongs to, with the answers to be entered.

If you need to enforce rules this can be done dynamically as well.

HTH.

 

[Arhat]

I think I've understood the first part. I'm still unsure about the 2nd (3-way junction table). Image2 has a relationship picture and image lists some entries for the Attributes table. Is this a correct understanding?

 

[Banana]

Hmm. No.

You're violating normalization with "Attribute1", "Attribute2"... There shouldn't be any repeating fields.

Furthermore, what are A, B, and C? I realize you're trying to be abstract but I have suspicions that A, B, and C could actually be in one table...