Hi,
It's good to see someone taking an interest in how to do this properly
It's obviously a large topic and not something i think i could cover myself but i can give you some pointers.
The first thing to do is analyse your requirements. The purpose of a data base is usually to store information about a system. Sometimes a system - a real world process or processes - will develop around a database but in the main the database is a model of a system that already exists.
There are a number of ways to model the system such as Structured Systems And Design Methodology [SSADM] but the aim is to indentify the boundaries of the process you want to analyse and the entities [objects/processes/data storage] that exist within the boundary and form the system. The outcome of the analysis should also give you the key entities and relationships between the entities. i.e. People buy Cars. 1 Person may by many Cars; one Car can be bought by many People [over time]. We've already indentified two entities People and Cars; 1 process Buying with an attribute time; and a relationship
The aim is to have a map of all the objects and processes that form the system, a logical model [Look up ERD]. For a large system this can have many many layers for something simple perhaps just one or two. It doesn't need to take long necessarily but it should be done at the start even if you are turning a spreadsheet based process into a database.
The logical model should model the real world and be independant of the platform - such as Access - that you will finally use. Be clear at this stage that if you are modelling an existing process then that's what your logical model should display; be as unbiased as possible about it. Try not to save the world at this stage.
Once you have your logical model you may decide that it shows you inefficiencies that can be looked at. This is a seperate process that can have wide implications for the business but is worth doing so that you don't end up implementing a database that needs changing late in the day when someone decides that your analyis indentifies issues. -systems always change but i've found this is a good point to get agreement that this is how the system works and that is what you are building your database on. If the system is changed at this point your logical model will also need to change.
Once you are happy that you have all your entities and the relationships between them you can start to look at the attributes those entities have. You may have already identified some that allow you to work out how the entities are related to each other but you'll want to analyse each entity and decide what other information you want to store about them i.e a customer entity will have a name age phone number etc.
When you have assigned attributes to your entities you'll then need to think about normalizing your entities.
You'll see that some of the data in the entities breaks 1st or 2nd or 3rd normal forms and that you have relationships between entities that require resolving with join/link/bridge -same thing- tables. You may find you need to create two entities from one in order to comply with normalization rules and you will need to look at unique indentifiers for your entities. [look up 1 to many]
When you start the normalization process you move away from the logical model to a physical model that may be biased towards the platform that you are intending to use. The normalization process will create structures that don't exist in the real world.
You'll perhaps realize that at this point we have created our tables and the relationships between them and primary keys - note there is a relationships window in Access that allows you to indentify these and enforce referential integrity and other things - look that up
-
You can then decide which attributes must appear for the entity it exists in and what data type they are. Each attribute needs to be stored and will need a type e.g text, number etc.
Now you can think about creating your database
Tables first.
Then relationships.
Then queries and forms
Then queries and reports
It's not really that hard you already did it.
You defined your system boundary i.e. you only wanted a look-up system.
You indentified the entity Formulas. These have attributes one of which is Components. This entity then needed normalizing so you created two entities one for Formulas and one for Components. You then needed to resolve that further so i added the link table.
There is a lot to this and i'm not particulalry good at explaining what goes on in my head but i can recommend a really good book. Got me through college
http://www.holbornbooks.co.uk/details.aspx?sn=1250285
Database Design & Management [using Access] by Nick Dowling published by Letts
HTH
K.