Project: Provision Expert - Can you help me through this project?

[AcousticBruce]

I just bought a subscription to Lynda.com and I am learning Access 2013 Essentials. I am having a hard time understanding how to set up my particular project.



Mission Statement
The Provision Expert database will provide Yacht Chefs the ability to easily plan weekly meal plans and generate accurate and extremely organized shopping lists.

Mission Objectives
·Provide an easy access list of user defined recipes sorted by tags.
·Print out a professional organized weekly menu.
·Print out an organized shopping list, separated by types of ingredients.
·Easily add, delete or edit recipes.
·Have a rating system for how much people appreciate a particular meal.

This is the basic idea and I am going to start with basic questions.
---------------------------------------------------------------------------

I am having a hard time understanding how to create a recipe and add ingredients. So lets start with ingredients. I am assuming because no ingredient will repeat, it might be ok in this situation to make the name of the ingredient field a primary key. With that said it will have these fields: Name, Type, and Unit. Unit, by the way, might also be a foreign key to another table for complex conversions, but this is hard to say, because I am so new to Access.

Questions....
1. Can the name of the ingredient be the primary key since they will not repeat?
2. If I create a recipe table, how would I add multiple ingredients? I have learned that you do not want multiple values in one field... SO how would these two tables relate?

Edit: I have already seen this template http://office.microsoft.com/en-us/te...001018635.aspx
Think about the ingredients being tags. There is going to be hundreds of ingredients and the recipes table will need to add any amount of these to each row. How might I go about this?

[June7]

Recipe database is been topic of numerous threads. Search form or web.

Review http://office.microsoft.com/en-us/te...001018635.aspx

Can use ingredient name as PK but how will you handle:
pepper, bell, green
pepper, bell, yellow
pepper, bell, red
pepper, jalapeno
pepper, cherry
pepper, banana

[ItsMe]

Use the Autonumber data type for your PK.

[AcousticBruce]

Recipe database is been topic of numerous threads. Search form or web.

Review http://office.microsoft.com/en-us/te...001018635.aspx

Can use ingredient name as PK but how will you handle:
pepper, bell, green
pepper, bell, yellow
pepper, bell, red
pepper, jalapeno
pepper, cherry
pepper, banana

Yes, I have downloaded this a while back and I cannot find any tables to learn from this. I have done numerous searches to try to find if someone made this already or find a good template.

I would name them like this...
Red_Bell_Pepper
Green_Bell_Pepper

Use the Autonumber data type for your PK.

Even if there is no chance of a PK being used twice?



Think of the ingredients being tags, there might be hundreds of them. I want to create a recipe table and add any number of these tags to each recipe. How might I go about this?

[ItsMe]

Aside from Primary Keys there is this thing called Business Key. A possible business key would maybe be a part number, eg AA-502-2014

This example of a business key works great while humans use the Business Key to select parts to be pulled, ordered, etc. This way they can get summaries of the parts being pulled from inventory without retrieving or having to read the entire part description. This number does not work so swell when the computer tries to retrieve parts from the table. Use data type Number not data type Text for your PK's and FK's. A good general rule is PK's data type Autonumber and FK's data type Long Integer. It is also beneficial to Index your Foreign Key fields using the properties while in design view of your table(s). It is not always a good idea to Index fields of data type Text.

Definitions of keys are super complicated. There are many types and often many synonyms. I am not fully explaining a Business Key in the above explanation. However, because you are considering using what would otherwise be a name and not a Key at all, I am bringing up Business Keys and Surrogate Keys. A good real world example of this would be Truck Numbers for a fleet of vehicles.

If a range of truck numbers for a fleet are 20 thru 50, as you are modeling your database, this field could be considered a Business Key (as well as a Candidate Key). As I consider the truck number a Candidate for the Primary Key, I will make my final decision to introduce a new field of Autonumber and introduce my Surrogate Key (Autonumber) as the Primary Key, forcing the truck number to be the Business Key and not use the truck number as a Primary Key.

http://www.youtube.com/watch?v=_aN-8kszIdA


Like June mentioned, you can use text as a PK but it is not always a good idea.

[June7]

What do you mean can't find any tables? How can there be a database without tables?

[AcousticBruce]

Aside from Primary Keys there is this thing called Business Key. A possible business key would maybe be a part number, eg AA-502-2014.......
Definitions of keys are super complicated....

http://www.youtube.com/watch?v=_aN-8kszIdA

Like June mentioned, you can use text as a PK but it is not always a good idea.

I will check this video tonight. I ask questions to understand and I am most likely going to use autonumber for pk but, I just want to know why. Good convention is usually the best thing to do. I am ok with that.

What do you mean can't find any tables? How can there be a database without tables?

Open the file. Where are the tables?
http://office.microsoft.com/en-us/te...001018635.aspx

EDIT!!! I just opened the file again and in the shutter bar there is a filter that only had "forms" showing. I now see the ingredients list and recipe list.

[June7]

Open the Navigation Pane and set it to show All Access Objects.

[AcousticBruce]

Ill watch the vid. And get started on some tables and ask more questions soon. Thanks everyone.