CODEWITHSUNDEEP
mysqlsqldatabase
steve
steve

Reputation: 113

SQL Constraints on multiple tables

I'm having difficulty in designing this table and the constraints for it.

I have a list of users, each user can have a list of preferred meals. In an attempt to determine what meal is cooked next, I've assigned the lists values.

Yummy <- value 5.
OK <- value 3.
Disgusting <- value 1.

I only want each user to be able to mark each meal in one of the categories.

Currently I've an incredible simple db which is:

User(id, name)
Meal(id, name)
Yummy(id, userid, mealid) where userid id and mealid are foreign keys.
OK(id, userid, mealid) where userid id and mealid are foreign keys.

What i'd like to do is make it so that If I insert lasagne into Yummy then I can't also insert that value into 'OK' or 'Disgusting'. I think that a constraint on both the mealid and the userid is possibly the right approach but I'm a little out of my depth.

Any help or suggestions would be hugely appreciated.

Upvotes: 0

Views: 102

Answers (1)

Uueerdo
Uueerdo

Reputation: 15961

You'd probably be better served with a database design similar to this:

user (id, name)
meal (id, name)
rating (id, name, value)
user_meal_rating (user_id, meal_id, rating_id) *primary key on (user_id, meal_id)*

Upvotes: 1

Related Questions