SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Addict danfran's Avatar
    Join Date
    Jan 2005
    Location
    New York City
    Posts
    244
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Enforcing Constraints (one step further..)

    Group,

    What I have actually done [in my database design] is to denormalize the relationship between items and the categories / subcategories that they fall into.

    Ex: Each Category has one or more Subcategories (one-to-many enforced by FK in SQL Server). Also, each Subcategory has one or more Items (also enforced by FK in SQL Server).

    What I have done is duplicated the CategoryID in my Items table, thereby producing a scenario where I must manually ensure that if an Item belongs to Subcategory X (and X is in Category Y), that the value that I've duplicated in my Items table matches Y.

    I understand that what I should really do is remove Category from my Items table and let the FK constraints do their job (and I'm considering it), but my question is, is there a way I can continue this duplication of data by using a feature native to SQL Server? More to the point: Of triggers, check constraints (if even possible), and periodic validation queries, is there a reliable way to patch this hole?

    Dan

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i'm totally lost, but that could just be me

    how about you show the table layouts?

    also, please explain why you want duplication of data

    this is normally not desirable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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