Need input on how to properly normalize my data in tables, structuring my database.

[trishamn63]

I'm in the process of normalizing my data into 2 maybe 3 excel tables (and then importing them into Access 2010 and creating the relationships/links there), and I had thought that the information I needed for my small startup was simple, but the more I'm looking around, I'm realizing that I may be in a bit over my head and can use some input before I make a mess.

Basically, I'm creating a very basic wine varietal database with a very basic profile of the wine, and the food that pairs with it will mostly be food that can be stored easily for tastings (cold and room temp food). Where I'm having trouble is that I'm "rating" how the wines (not every wine is a "match" by using a scale of +2(Perfect), +1(Positive), 0(Neutral), -1(unpleasant), -2(avoid), there will be fields that are empty with no "match" or relationship between the food and wine, while some matches will have many. I have several reference books and my own notes that I will be using to enter the data.

Currently, I have my "wine" data in one "table". Fields are:
Wine_ID, Wine_Name, Wine_TastePro(file), Wine_Color, Wine_GrapC, Origin, Noble (Y/N), Tasting Notes.
For the food, I have:
Food_ID, Food_Catagory, Food_Name, Food_Prep(aration), Food_TastP(rofile), Food_Temp, Food_Notes.

What would be the best was to go about linking the data? Should I create a separate table with my "ratings" and then enter each possible "pairing" in it's own field using the id's of both. Ideally, I would like to be able to generate a "Wine" report and bring up the different "food" matches, from best to worst, sometimes maybe only the best match, and vice versa, I'd like to bring up a food item and find the best wine pairings.

My brain is having trouble making the next step, I've worked with databases in the past, pretty deeply in the design but not development stage, but it's been 20 years. I think that having this information at my fingers instead of spending the next 3 years at Sommelier school would help me make a go of my little catering business. I appreciate any assistance you all can give me.

 

[plog]

Should I create a separate table with my "ratings" and then enter each possible "pairing" in it's own field using the id's of both.

Yes, that's it exactly. It would look like this:

Wine_ID, Food_ID, Rating


Then to create your query you bring in your 3 tables linking the Wine_ID fields and the Food_ID fields. You get the Wine data from the Wine table, Food data from the Food table, Ratings from this new table and you could even apply criteria to any of the fields.

 

[trishamn63]

Thanks for getting back to me so quickly, it's like a light bulb went off in my head, I knew I'd created the pieces to the puzzle, but I was having trouble seeing how they would fit. Once I get my data into my database, which will be time consuming I'm sure, I'm sure I'll be back here when it's time to design my forms!