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.