I am new to database design but am trying to create a database to match the aromas in wine to a number of attributes of grape varieties
I have a list of 200 aromas that you might find in wine (things like raspberry or mint etc)
I have a list of grape varieties (shiraz, cabernet sauvignon etc)
I know that I can create a table for each and another table to show the joining of the two tables (you may get mint in shiraz and cabernet)
However, and this is what is throwing me - the aromas fit into categories such as primary (related to the grape), winemaking (things that occur during winemaking), developed (as the winer ages) for each grape variety. These are not exclusive though because some aromas that are primary aromas for one grape variety are developed aromas for another. Frustratingly as well, some aromas occur in 2 of these categories for the same grape variety.
The aromas also fit into categories according to type eg. fruit, nuts, berries, earthy etc
Furthermore, each grape variety (such as shiraz) can have different aromas if it is grown in a cold, cool, warm, hot or very hot wine climate.
The outputs I require are:
1. If I click on a grape variety I can see all the aromas for that wine
2. A report that shows the grape variety and the aromas arranged under the headings of Primary, Developed and Winemaking
3. A report that shows the grape variety and the aromas arranged under the headings of cold, cool, warm,hot and very hot
If anyone can suggest a database structure that can accommodate all of these things or point me towards a fix, I would appreciate it