Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2010
    Posts
    16

    Enforcing Constraints...

    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.
    Last edited by TomIsBigInTheGame; 02-22-10 at 10:52.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    What happened to stop you using the AMD by the way?

  4. #4
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    You should be able to use a trigger to enforce the constraint instead of using the front end.

  5. #5
    Join Date
    Feb 2010
    Posts
    16
    Quote Originally Posted by pootle flump View Post
    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.
    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.

    Quote Originally Posted by andrewst View Post
    What happened to stop you using the AMD by the way?
    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.

    Quote Originally Posted by MarkATrombley View Post
    You should be able to use a trigger to enforce the constraint instead of using the front end.
    Hero..! I knew I learned about triggers for sooomething...

    Cheers.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •