I am a little confused on the subject of supertypes and subtypes. When examining the data that needs to be structured, it was clear that there were some simple supertype-subtype relationships. I am afraid I keep seeing more supertype-subtype relationships and thus am getting closer to creating a far too general "objects" table, which I'd imagine is bad design.

Let me explain with an equivilent example. Firstly I could beging with tables "Cats","Dogs" and other Mammals with the supertype table "Mammals". Then the research scope explands, so an extra table is needed called "Animals" and other subtypes such as "reptiles" are needed. Initially, all the surrogate Ids were generated ion the Mammals table, but now this needs to be changed to the Animals table. Perhaps "reptiles" were on a seperate database before, so their surrogate ids need to be replaced with new ones generated by the "animals" table. Perhaps further into the furture the database might also contain "Plants", so again, all the ids might need to be regenerated in a table called "Organisms"

Is this the right approach? Other issues I have here are moving the common attributes to super tables. The "Mammals" table could have orginally contained the column "name" which will need to be moved to the table "Animals" as it is a common attribute of all animals. This will then need to be moved again to the table "Organisms" when that is introduced. The issue here is the most super table, in this case "organisms", will need to be accessed whenever a name of anything is needed, which might slow things down.

I'd be really interested to know you opinions, especially concerning regenerating ids.

Thanks,

Mike

Let the attributes you need to capture determine how many tables you need to create. Create a new subtype or supertype table whenever you have a unique set of attributes that needs to be captured (unique to some new subtype I mean).

For surrogate key generation, does your DBMS support sequences? You don't necessarily need to tie key generation to any one table.