Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
michael.appleton
Posting Yak Master
160 Posts |
 Posted - 2009-05-06 : 11:55:57
|
| 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 |
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 2009-05-06 : 12:42:20
|
| Design according to your application & functional requirements. |
 |
|
|
|
|
|
|
|
Object Supertypes/Subtypes