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:

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

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

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?

  • 1
    you need to propagate ProviderId into your UserRating table, and have a unique constraint on UserRating(UserId, ProviderID) – A-K 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. – Trygve Laugstøl Nov 29 '12 at 18:01
  • @trygvis good point, but I only had a couple of minutes. – A-K Nov 30 '12 at 19:57
  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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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