Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have 3 tables which are related and a constraint on one of them which I can enforce in software but I don't know how to enforce in the database. I have a series of providers who send a set of data. A provider may send multiple schemes. So the pair of provider and scheme is distinct. A scheme consists of many records - let's say Credit Ratings. A user is associated with many credit ratings but only 1 from each provider/scheme pair. I'm stuggling to relate this data. What I currently have looks like:

Provider
========
id, providername, typename
1, Provider 1, type1
2, Provider 2, type1
3, Provider 2, type2

CreditRating
=======
id, providerid, rating, rank
1, 1, A, 1
2, 1, B, 2
3, 1, C, 3
4, 2, AA, 1
5, 2, B, 2
6, 3, A+, 1
7, 3, AA, 2

UserRating
==========
id, ratingid, userid
1, 1, 1
2, 1, 2
3, 5, 1

Now what I want to enforce is that the UserRating table, for each user there's only one credit rating from a given provider/type pair. With my current design I can't do that. One option would be to add the providerid to the userrating table but then I can't ensure that this is consitent with the providerid on the credit rating itself.

Any ideas?

share|improve this question
1  
you need to propagate ProviderId into your UserRating table, and have a unique constraint on UserRating(UserId, ProviderID) –  AlexKuznetsov Nov 29 '12 at 15:26
1  
@AlexKuznetsov - if you have an answer your should create an answer post for it so it can be upvoted and accepted. –  trygvis Nov 29 '12 at 18:01
    
@trygvis good point, but I only had a couple of minutes. –  AlexKuznetsov Nov 30 '12 at 19:57

1 Answer 1

  1. Include the providerid column (and appropriate values) in the UserRating table.

  2. Make (userid, providerid) a unique key for the UserRating table.

  3. Make (id, providerid) a unique key of CreditRating.

  4. Make (ratingid, providerid) a foreign key of UserRating, referencing (id, providerid) in the CreditRating table.

Also, make (providername, typename) a unique key of Provider, if you haven't already. Or better yet, normalize them into separate provider and type tables, with a many-to-many join as appropriate.

share|improve this answer
    
+1 This should work. –  AlexKuznetsov Nov 29 '12 at 15:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.