X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Normalization, Access 2016    
post Dec 20 2017, 06:35 AM

Posts: 60
Joined: 15-June 06

I have read books, read articles, and watched videos trying to wrap my mind around normalization and I guess I am not smart enough!

Could someone please try to explain it in simple words (like I'm a 5 year old)?

I need to build a database for a meat processing plant (small operation) and I can do the main build, but it always comes down to someone telling me I don't have normalization. I would like to build this one right from the jump go.

Go to the top of the page
post Dec 20 2017, 07:04 AM

Posts: 1,669
Joined: 5-February 06
From: Ohio, USA

"There should be only one."

The rest are related by index.

I know. Still confusing. Without knowing what you've read, or exactly where your difficulties lie, I'll start ... here....

Picture your database in a spreadsheet instead, such as Excel. Let's say, one big spreadsheet, containing customers, what they order, what products are processed in the plant, all in one big sheet.

Ugly, right?

So you break it up into tabs... customer orders, products.

No, wait... customers live somewhere. Customers on one sheet, orders on another sheet, products on a third. There. That's got it, right?

So... what information are you putting on the order sheet? Customer address?

What happens when the customer calls and changes their address, while they have an outstanding order? Sure, you change it on the customer tab. But did you remember to change it as well on the order? Oooops... The delivery man just delivered 20 pounds to the wrong address.

Why was the mistake made? Because we had the address stored in two different places. If we'd had it stored in one place, and just linked to it somehow...

There. There can be only one. The rest is linked, so that if I change it in the main table, say, CustomerAddresses, then it'll be changed whenever someone queries the database.

That's a pretty simple example.

The first piece of advice I usually give when someone wants to learn Access, or about databases, and how to design and build the tables, is to learn SQL. Learn about how you're getting your information out of the database, like customers and their orders, processing and inventory... and when you design your tables with all that in mind, 'normalization' may be clearer. I picked up "Teach Yourself SQL In 24 Hours" at a thrift store for a dollar.

You can always throw up a table design here, even with no information, and get critiqued. There are lots of helpful people around here.
Go to the top of the page
post Dec 20 2017, 07:40 AM

UtterAccess VIP
Posts: 7,785
Joined: 24-May 10
From: Downeast Maine

I don't know what you have read, but have you tried the UtterAccess Newcomers Reading List? There are a number of good articles in there, but a good starting place is Crystal's tutorial (the second one in the third group of links).

Nick has provided some solid insights, but there is more to it than can be summarized in a few paragraphs. One thing to keep in mind is that a table is for storing information about an entity (customer, product, employee, etc.), and attributes of that entity. A customer is an entity; an order is not an attribute of the customer, but rather a piece of information that is related to that entity. Therefore orders need to be in their own table. Same for orders: an order is not an attribute of a product, but rather is a separate thing.

A description of your tables and relationships may help guide the responses and suggestions.

On another note, smart is recognizing that you need a better way to manage this. Perhaps you have been using spreadsheets, and recognize the limitations. Whatever the reason, you know a better way is needed. That's smart. The rest is knowledge and experience, which will come via study, questions, and time. Nobody was born knowing this stuff any more than they were born knowing about carpentry or cooking.
Go to the top of the page
post Dec 20 2017, 08:04 AM

UA Admin
Posts: 34,299
Joined: 20-June 02
From: Newcastle, WA

Well, there are various levels of formality you can consider, so it's not surprising that the whole thing can get fuzzy.

There is a full set of rules about what normalization is and how to apply it, and if you try to slog through the abstract, it can get confusing. Especially when experts can't always agree, or at least use the same definitions and explanations for things.

So, the basic idea is that every bit of data of importance to you is stored one time in one place. My favorite analogy is Grandma's Cupboard. She kept it well-organized. There is a shelf for condiments, a shelf for canned fruits, one for canned vegetables, one for spices, and so on.

Let's look at the sweetener shelf. It has on it: White Table Sugar, Brown Sugar for baking, Powdered Sugar for pastries, Honey for tea, and so on. But she doesn't put Honey in two different places in the cupboard, and she doesn't mix sweeteners with dry goods (like rice). In other words, if she needs something to cook with, she goes straight to that one place to find it.

Another important thing that has to be accounted for is the problem of Repeating Groups. Here, the best way to think of it is that Excel spreadsheets are "flat" and that leads to "columns on the right" thinking. For example, you've been processing cattle, but you decide to add pigs to the operation. So you have a column in your "table" called "BeefLiveWeight", where you track the live weight of each beef animal as it comes in. Now, with "columns on the right", or "spreadsheet" thinking, you'd just add a column for "PigLiveWeight". Oops, that's a problem because now, there will have to be an empty field under one or the other for each new animal you add. It can't be both a pig and a steer, so it can only have one kind of LiveWeight. We call such tings "Repeating Groups" because "XXXLiveWeight" gets repeated for each type of animal processed.

The saying that sums this up is "Columns are expensive. Rows are cheap." What that means is you'd have a table for "AnimalType", which has one or two fields in it, along with a Primary Key. AnimalType would simply be "Beef", "Pig", and later, when you expand further, a new row, or record, for "Sheep", and then one for "Goat". That way you avoid the expensive of adding "SheepLiveWeight", instead adding a cheap row for sheep, and so on.

From there, the process can go in different directions because the business rules that govern your operation play a major role in deciding what does and does not need to be accounted for.

For example, does the business you are in need to track disposition of packaged meats through to the retailers who purchase it from you for recall purposes? If so, do you need to include a way to identify which package came from which animal and, in turn, where you obtained that animal, i.e. which feedlot operator sold it to you?

But, assuming that you have a sufficient understanding of the business rules you need to apply, that part's up to you.

One more important thing to consider is how you identify each record, i.e. each beef animal. Do you have ID's of some sort, ear tags maybe? Are they universally unique? Will two animals ever come into your operation from different feedlots with the same ear tag numbers? Or is there another way entirely to track that, perhaps by lots of several animals? The point is that you need a unique identifier for each animal unit you process. And again, here I assume that's important. If you only care that you took in a lot of twelve Angus steers from Grass Valley Feedlot, but not which twelve, that's a different requirement that results in a different table design. In any event, whatever level you do need to track, you need a unique identifier for it. If ear tags are universally unique, and you can track them that way, that might serve as the Primary Key.

So the next thing to consider is the interaction between the first principle I laid out, "One place for everything and everything in one place", and the idea that you may track slaughter animals individually or by lot. Here's what can happen. You need to track internally by lot (these twelve steers were slaughtered in the same shift in Plant A on December 20th, 2017), but also know which ear tags were in that lot. You don't track them individually past the intake, but you do need to track that lot, and be able to track back up the chain to find those ear tag numbers. Here's another place people often go wrong. They either violate the principle that there is one place for everything, and everything in one place by putting all twelve ear tags into a single field: 123, 222, 345, 665, 157, etc. Or, they resort to spreadsheet thinking and have "AnimalOne", "AnimalTwo", etc. Either way, the trap has been laid for future problems. Instead, what you would need is a child table for Ear Tags in a lot. This one has a field for the Primary Key (this can be an arbitrary value as long as it is unique within that table), a field for the Foreign Key to identify the Lot to which that Ear Tag belongs, and of course, a field for the Ear Tag. Note that this table is going to get very tall, with many records, one for each Ear Tag you process. If you process 100 animals a day every day for 100 days, you'll end up with 10,000 Ear Tag records, if I do the math right. Tall, but cheap in database terms.

As you think through each aspect of your processing operation, the same underlying ideas apply.

Grandma's Well Organized Kitchen and lots of cheap rows and few expensive columns.

The formal rules of normalization will help guide you to that result.
Go to the top of the page
post Dec 20 2017, 08:14 AM

UA Admin
Posts: 34,299
Joined: 20-June 02
From: Newcastle, WA

Oh, I meant to say something about the comment that you're not smart enough.

If that were the case, you'd be plowing ahead with an inappropriate table design. You are smart enough to recognize the need to deal with it, and smart enough for look for additional help.

Everyone here started in pretty much the same place. I made some horrendous errors in my initial databases. Cringe-worthy. In my very first Access application, I tied "address" to "contact" instead of to "business". When a contact left for a new job at a different business, that address went along for the ride in our database. Oops. No one was happy. I think the term "stupid" came up in the conversation where I was notified of the error.

So you should relax and consider it a chance to grow some new knowledge. You don't have to become an expert, just learn enough to be effective.

BTW, one of my favorite database design books is Database Design for Mere Mortals by Michael Hernandez. I've given away every copy I ever bought, so I stopped buying it and just refer my friends to it.
Go to the top of the page
post Dec 20 2017, 08:23 AM

Posts: 1,881
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


You have been given lots of advice re Normalization and analogies. Could you please describe your small meat processing business to us? We are not familiar with the "business", so please tell us in plain, simple English --just as you would use with a 6 year old. (No jargon please.)
This post has been edited by orange999: Dec 20 2017, 08:23 AM
Go to the top of the page
post Dec 20 2017, 09:39 AM

Posts: 18,324
Joined: 29-March 05
From: Wisconsin

Echoing orange999's request; feel free to tell us what sort of data you're working with that you want this database to track.

Just the act of describing the data, and how it all relates, will help you to Normalize your data.

For example, if you worked in a cake shop, your goal might be to track purchases of ingredients, the recipes for creating different kinds of cakes, and the invoicing for sold cakes. That's a good broad stroke view. From there, you can look for the logical groups of things, like Ingredients, Suppliers, Bills for purchased ingredients, Cake types, lists of ingredients and quantities for each type of cake, and Invoices.

Again, if you describe your plan in plain English, we can help you translate that into a Normalized set of tables.

Hope this helps,

Go to the top of the page
post Dec 20 2017, 11:02 AM

Posts: 1,495
Joined: 7-April 10
From: Detroit, MI

Rhonda, all of the suggestions here are great. Best thing is to begin the process yourself, then post your tables and relationships along with a description of your business and needs (like doctor9 suggested). From that, there are many people who can guide you to get it set up the best way possible. Soon enough you will be able to understand 'normalization' and how important it is.
Go to the top of the page
post Dec 21 2017, 03:01 AM

Posts: 293
Joined: 3-May 17
From: France

This is how I would try and get a five-year old to handle this:

You learned to crawl, then to walk, and now you are learning to run without falling back on your knees.

There are 3 important rules to normalisation.
Start by learning the first one off by heart, and practise it until you understand.

Then learn the second, practise, understand and when you're ready to move on, same thing for the third.

You would like to "build this one right from the jump go", but results will come quicker if you take it one step at a time.
This post has been edited by Jaiket: Dec 21 2017, 03:09 AM
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    16th January 2019 - 07:23 PM