MySQL constraints and derived values advice needed

I have recently started playing with databases, trying to teach myself using examples.

I have the following problem that I'm currently trying to solve which confuses the hell out of me and I'm hoping that someone can shed some light.

The database has 4 tables. Photographers, Pictures, Competition and Viewers. The concept is that photographers take part in competitions. They shoot 1 picture for each competition and viewers rate it. The winner of the competition is the one who gets most points. I have put the following restrictions.

1. Ratings are between 0 - 5
2. Only the first 20 viewers can vote
3. I don't want to store the total rating of the photographer as I want to learn how to calculate开发者_开发知识库 derived values.

For numbers 1 & 2 - I'm not sure how to explicitly create this constraint. For number 3, I don't know how to represent derived values on the db. I'm using MySql

Any thoughts, advice would be greatly appreciated!

Best Regards

1] You can't set such constraint directly in MySQL as far as I know. you could use UNSIGNED TINYINT with allowed values of 0-255 but you have to check yourself if the values are ok before inserting them into the database.

2] Do I understand you correctly that you want to allow only a maximum of 20 votes per picture?

You'd either have to store all the votes in a separate (then check for the number of votes already present) or save just the average and number of votes. Either way, you have to check for this yourself and determine if you allow the user to vote or not. For the first option:

SELECT COUNT(*) > 20 FROM votes
WHERE picture_id = '123'

For the second even simpler:

SELECT votes_count > 20 FROM pictures
WHERE id = '123'

3] If you had your votes for the pictures stored directly in the pictures db, you simply use the SUM() function:

SELECT *, SUM(points) as total_points FROM photographers AS ph
LEFT JOIN pictures AS pic ON pic.photographer_id = ph.id
GROUP BY p.id, competition_id

I hope I made no mistakes there. If you have more detailed question, please just ask, I'll try to fill the holes ;)

1) Take a look at enum values, although tinyint(1) unsigned might be faster