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 - 2008-12-08 : 10:25:05
|
| Hello,I have a problem with subtypes and supertypes. I have several tables that represent objects that can be connected by a pipelines, for example, Houses, WaterWells, Waterpumps, WaterStorageFacilities and Pipelines themselves. The pipelines table contains colomns ToId and FromId, which are the ids of the connecting objects. This works as every pipeline connectable object has a unique id compared to every other pipeline connectable object, so in effect they are all subtypes of the supertype WaterInfrastructure. WaterInfrastructure is also a table with an identity column, where all the ids are generated, and a type column, so that it is clear which table each Id belongs to. The issue is I can't think of anything else to put in this table. Apart from very general attributes, like name and entry date information, all these objects share in common is that they are connectable to water pipelines. The problem is, that I have essentially created a table that only stores Ids. Is this really a good design? If not, what is the alternative? I know I could create some kind of custom counter for ids, but this must be quite a common problem so I expected there to be some in built database mechanism to deal with this issue.Thanks for the help,Mike |
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2008-12-08 : 12:25:11
|
| You could leave it as is if it works and makes sense to you. Later each table may start to evolve in different ways as the business requirement grows.If you're sure that wont happen, you could just use one table like this (Along with your Pipelines table) if you really wanted to (but in this case I dont think I'd advise it):table WaterInfrastructuresObjectIDObejctType (Houses, WaterWells, Waterpumps, WaterStorageFacilities ,Pipelines) |
 |
|
|
michael.appleton
Posting Yak Master
160 Posts |
Posted - 2008-12-08 : 12:35:15
|
| I'm sure if I explained myselft very well earlier. All pipeline connectable object tables (houses, waterpumps etc.) have lots of different columns already. My issue is the WaterInfrastructure table is exactly as the example you give; it only has columns objectid and objecttype, and I can't think of any other shared attribute to put in that table so it is basically just an id generation table.Or perhaps you know what I meant and I don't understand what you mean!Thanks for the help,Mike |
|
|
|
nathans
Aged Yak Warrior
938 Posts |
|
|
|
|
|
|
|
Subtypes and supertypes