Edited (slightly!) for brevity...

I am currently modelling a database for a configuration management system. It stores / presents the following:

Information about items (i.e. their attributes) within the company infrastructure, e.g. virtual servers, hardware, etc and relationships between items (i.e. how the items physically interact within the infrastructure).

I want to achieve a utopian design () where the backend is entirely independent of its implementation, as does every developer at this stage of the game.

I'll focus only on the relevant relations for the purposes of this query, ignoring everything else...

Items have an ItemID a description and an ItemType_ID and some arbitrary base-class attributes.

Items can have an ItemRelationship, which comes with an ItemRelationshipID, RelationshipType_ID, SourceItem_ID and TargetItem_ID. Both source and target item IDs reference Item(ItemID).

RelationshipTypes have a RelationshipTypeID, a description, and something to depict significance of the type of relationship (e.g. to model the fact that "<Virtual Server A> IS HOSTED ON <Physical Server A> might be deemed more significant than <Web Service A> LOGS ERRORS TO <Database A>, according to the relevant business decision, and would be awarded a higher value).

I would like to introduce the idea that RelationshipType depicts the ItemType of the source and target items. I imagine I could introduce another attribute to RelationshipType for this quite easily, e.g. RelationshipSourceTarget_ID linked to RelationshipSourceTarget - a lookup of all possible source / target ItemType IDs... However, I can't think how to actually enforce this constraint. I can see that I could make an insert stored procedure which performs a lookup of the type of each item to be included in a proposed relationship, and checks for the ID of the relevant RelationshipSourceTarget, and then check this matches for the given RelationshipType. If anything's wrong, throw an error (using Raiserror in MSSQL, probably).

However, is this really a constraint? Technically I don't think so - people could still enter conflicting values into the DB via manual INSERT statements... Is there a way to enforce this constraint on a relational level? Of course I'm aware I can enforce this via a front end, but that doesn't really improve the situation. Is it enough to have a SP to enforce this integrity and move on? I'm not sure.

Thanks in advance... And be nice..! Many of my previous projects have been academic, and I've not been in industry for too long. xD

If the above is too confusing / nonsensical, perhaps I can attach an ERD...

Cheers.

Sorry dude - I only made it half way through. But it sounds like you want to look at sub-type super-type. This is a relational solution to "inheritance". Note that others here will also simply advocate having all the column in one table and only populate the relevant ones.

pootle flump ur codings are working excelent.

What happened to stop you using the AMD by the way?

You should be able to use a trigger to enforce the constraint instead of using the front end.

Haha, it turned into a bit of a marathon post, so fair enough... Friday afternoon --> rambling. Edited some fairly irrelevant info. out. You're right though - there is a generalization heirarchy, but that's a separate topic.

Time..! After speaking with Simon Williams, I knew it was beyond the scope of this project. I'm a bit disappointed (personally speaking) but hey... Essentially, I need to complete this project within a short timeframe, which means sticking somewhat to what I know and what I have available, so I constructed an argument to that effect. I'm still interested in the AMD though, and I might see if it's feasible to do some work with it next year for my dissertation or something.

Hero..! I knew I learned about triggers for sooomething...

Cheers.