A Database with days and corresponding prices

I need advice from you guys. I want to develop a database, which will store about 20 items and there are 5 seasons. E开发者_如何学运维ach season is about 2-5 weeks long. To each season corresponds particular price. For each day of the year database will store information about booking (available/booked/under usage) And application will dispaly visually information chosen by the user for a particular day.

id |_____name____|  01.01 |  01.02  |   01.03 | ................... | 12.31
_____________________________________________________________
1  |_____unit1___| 

How should I design my database for storing information for whole year. Besides, I need to keep it flexible becasue of February 28 and 29 days change.

You don't want a column for each day of the year. Instead, I would probably create three tables:

items:
id
name

seasons:
id
name
start_date
end_date

prices:
item_id
season_id
price

If your seasons vary from year to year, then you'll want to explicitly specify every season each year (so 5 rows per year). Then in the prices table, you'd enter the price, with a foreign key to both the item and the season. Then to get today's price for a given item:

SELECT prices.price
FROM prices
JOIN items ON (items.id=prices.item_id)
JOIN seasons ON (seasons.id=prices.season_id)
WHERE item.name="unit1"
    AND seasons.start_date >= NOW() AND seasons.end_date <= NOW();