My own favorite method follows what Pat said in terms of the goals. My method is quirky, but it works for me. I like to use a dry-erase board and a big box of sticky notes. (Things a business is likely to have and provides leftovers that the business can use later when the design is complete.) Then I sit down and look at the problem to identify what I will have to represent in the DB.
Represent? Why that choice of words? Because for non-trivial DBs, you are looking at making a computer MODEL of your business. The DB will have to somehow represent the entities associated with your business.
You have customers. They need to be represented in the DB. Usually as a table. You might have suppliers. Another table. You might have sales reps. Yet another table. OR if you always work with people, you can have a people table for each person you interact with, and separate tables for customers, supply reps, sales reps, advertising reps, etc... and each one can POINT to a person if you wish. Or, you can have people and companies in separate tables, and an intermediate table that describes their relationship to you. Like if a sales rep is also a customer. BUT all of this depends on whether your business model even CARES about that sort of thing, or whether it happens often enough to make a difference. And only YOU know that. But by drawing it out first, you can explore what you think is worth tracking and what is beneath your notice.
You have widgets with various model numbers that you sell. (I'm assuming for the sake of argument...) You need a widget table. You can sell more than one thing at a time, usually detailed on a sales invoice. That leads to an invoice table. You can sell more than one model and quantities greater than one on any given invoice. That means invoices have line items - and a line item table that is a child of invoices.
You get things from a supplier. That means purchase orders. Probably purchase line items distinct from sales line items. Do you allow for incomplete shipments in or out? That means a bill of lading makes sense because you need to indicate what comes in or goes out the door separately from the purchases and sales.
Every time you 'discover' a new business entity, whether it is a massive entity or a line-item equivalent, you have identified a table. The dry-erase board comes into play when you are trying to figure out what your business rules require you to do. Write each table name on the board. "Populate" it with the sticky notes, one note = one record.
Be sure you are up on normalization rules when you populate a table. That way you won't try to mix apples and oranges.
Now draw lines where you wish to keep data for some purposes. Let me 'splain that...
Let's say you realize you will want a report of average sales of each model so you can project stock requirements. That means you need to have the ability to identify the individual models in the invoice. So that means the model identifier, whatever it is, must appear in the line item for the invoice. 'cause you can't report on what you don't keep. Now, you'd say that I'm belaboring the obvious. But when you get caught in the weeds, it is hard as Old Billy Hell to see past the nose on your face.
Let's say you want to know what your customers buy, broken out by customer. Well, that means there has to be a link between the sales and the customer. OK, let's put the customer's ID in the invoice. Then the invoice number can be in the invoice line items. So with at most two links, you can tie line items to customers.
The point is, it is the DISCOVERY phase where you do your most important work. That is where you discover things you need to keep for later use.
Do you need to track individual items of the same model by serial number? If so, your inventory needs to track serial numbers. Are your items fungible? (I.e. one example of a particular model is as good as another of the same model AND serial numbers don't matter?) Then you DON'T need to track serial numbers. See how this works?
Why do you do this? Because of the old Access programmer's rule: If you can't do it on paper, you won't do it in Access.
So we have all this stuff on the dry board... what do you do with it? Well, add entries to various tables. See what is required when you add one item to one table. See what, if anything, has to be done to/with/for any of the other tables. In other words, work on the interactions. This will help you discover possible relational integrity needs. As invoices are added, you can see your business data flow and recognize its implications. As partial shipments come in, you can see how and where to track things.
And THAT is when you begin to reach the point where you can implement the DB that will help you down the road. That is when the model begins to take shape in your mind - with the clarity needed to help you build the model in the purely abstracted innards of Access.
The whole goal, as mentioned earlier, is to avoid design problems. Nicklaus Wirth, the father of the Pascal language, once stated ('bout 30 years ago) that data design flaws can account for as much as 80% of all programming difficulties that occur later in a project. Which is one reason why ol' Nick was a fan of strict-type languages. (Ada and Pascal are. C++ is not.)
Good luck!
I've currently gone back to the drawing board, and started planning out the entities, which reminded me of some stuff I learned a couple years ago in some of my classes.
