How to constrain data

Question

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?

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.