Confused on how to structure...please help!

[MR_G]

Hello everyone, I have often referenced this site for all sorts of problems and always find it helpful. I am probably all of your's worse nightmare, a self taught / hack developer that has somehow wormed his way into a decent job doing simple Access database work for a company that is completely Access illiterate. All that said, I love working with databases and I keep trying to improve my skills.

My question is actually about some freelance work I wanted to do for a friend. I designed a Food and Beverage database that records daily receipts (Item names, cost, salesprice, etc.) and then offers a "View Data" page where the user can query all sorts of data at once. I have done most of the work and it works great. The View Data form is a bit heavy, but the problem that just hit me was in the sheer amount of data that needs to be collected.

Just a quick breakdown looks like this:
Average Items / receipt = 5
Average Receipts / day = 50
Number of Stores = 4
1 Year = 360
______________________________________________________

= 360,000 records / yr

Any suggestions for a novice developer on how to make something like this work?

Thanks again for all the help in the past and for any suggestion!

 

[georgedwilkinson]

Split your database and normalize.

You might consider using a heavier duty DBMS for the backend. But Access can handle it if you normalize properly.

 

[The_Doc_Man]

1. Normalize, normalize, normalize.

2. Read other threads in this section of the forum where many of us have posted discussions on how to analyze a business model into its components so you can represent those components in an efficient way.

3. Consider how you will gather that dataset. Hand data entry? Electronic analysis of input files?

To my way of thinking, you will face two big issues.

A. Data input and validation of same.

B. A usable format for display of data, since as you point out, you have lots of records to be shown, summarized, whatever.

You need to resolve these early in the process. The actual management of 360K records per year isn't so bad if you normalize the pants off it. Access can do that in its sleep - IF you get the normalization right.

If you are in doubt, continue the thread with specific questions or start a new one. But the first place I would go if you haven't gone there yet is a search for 'database normalization' articles. Two places right away: Access Help has a primitive discussion on same. Wikipedia.ORG has some details as well. After that, a Google or Yahoo search (or your other favorite search engine) should deliver a gazillion hits on that topic.

If you have already seen (and followed) this advice before, start your analysis and normalization phase. If it is new to you, then start reading the top articles you find in your search. I would limit what I read to .EDU domains of colleges/universities you know and trust. I might also consider known database vendors from the .COM domain. Once you have read enough articles that it all seems to be repeating the same thing over and over, you have absorbed what you need to get started.

I would always advise for Access DBs to never shoot for less than 3rd normal form. If you think you have a requirement for 4th normal, that's OK, too. But if you don't reach at least 3rd normal form, you will have lots of trouble down the road.

 

[MR_G]

Thanks everyone,

Yes... I have currently been reading article after article on normalization. It is amazing how much you can do in Access without normalizing. I now see the key to normalizing, but I got a ways to go before I can do it efficiently. Any further advice would be of great help.

As per The Doc Man's suggestion, let me just put this out there for anyone that might want to respond, simplified, it goes like this:

tbl_ItemType (This is say FOOD, BEVERAGE, etc and ITEMTYPE_ID)

tbl_Food (These are my food items and include NAME and FOOD_ID)

tbl_SalesItem (This is where things are getting tricky. I want this to include ITEMTYPE, ITEMNAME, COST, SALESPRICE, and SALESITEM_ID)

tbl_SALES (This should be the table that users dump into from a form. They select the SalesItem and Quantity. This table also includes a RECEIPTNUMBER, but it is not unique because users to be able to put many individual items on one receipt, don't know if it was necessary to say that or not, but I thought the more info. the better)

What I want in the end is a way to Query Sales by ItemType, specific Item, or by RECEIPTNUMBER, Quantity, etc. being able to SUM, AVG, etc.

That was a simplified version. Ideally, I want to do a similar format with expenses and then be able to derive average costs for menu items using expense data over time, keep track of inventories, etc. But I will start with the basics.

I feel as though I have really stumbled on the normalization thing. My SQL is okay and I have a pretty good knowledge of how to work VBA and the technical aspects of Access, but the ideas behind normalization of data are just now on my radar screen. I appreciate everyones help and I am determined to get my normalization skills up to par.

Thanks in advance.

 

[The_Doc_Man]

Let me explain why you need to normalize.

First, when there is a one/many or many/one relationship, a properly normalized set of tables saves you space because you don't need to repeat the data from the "one" side of that relationship. All you need is the prime key (PK) from the appropriate "one" side table acting as a foreign key (FK) in a field in the "many" side table. And that FK uses the copy of the PK as a stand-in or surrogate for ALL of the data in the one-side record.

Second, normalization allows you to retain data on things you didn't happen to sell this month even though they were on the shelves. Because it isolates items so that non-use doesn't cause them to vanish.

Third, normalization (eventually) lets you define business relationships among the entities of your model. Which gives you a mind-check on reality. Or a reality-check on your model, either way. The ideal is to identify the relationships well enough that the model matches the territory in every significant way. When it does, everything will be a LOT smoother. But word of warning: Trying to build that model leads to many pitfalls before you get it just right. Don't be afraid to have a meltdown moment, take your sheet of drawing paper, wad it up, and violently throw it against the wall.

Your goal is to get Access to have a virtual model of your real business. The only way to get there is that you must know enough about the model to be able to tell Access the right thing to do at every turn of the way. Because you can't do anything in Access that you couldn't have done on paper first.

 

[Rabbie]

Further to The_Doc_Man's excellent advice you will find that it is much easier to add new queries/reports etc to a properly normalised database. As you have a working knowledge of SQL you should have no problems in retrieving the data you want in a query/recordset.

Good luck

 

[MR_G]

Just wanted to say thanks again to everyone. Especially to the doc man. I'm gonna keep on truckin' and yes, I fully expect a meltdown moment and I have already warned the walls.

 

[The_Doc_Man]

I hate to think about how many walls have paper dents in them in my office.

Seriously, without trying to reinvent this wheel too often, you must look at a serious design project as an interative process, one of continuous refinement.

The ART (notice not SCIENCE) of design is in getting a "feel" for the moment where you know enough about the project to start committing it to electronic methods. If you start too soon, you will reach a point where you say, how do I make THAT happen? If you start too late, the boss starts breathing down the back of your neck like a hungry jackal who has just found his next prey in a vulnerable position.

One method I often suggest is to find a big white-board and some dry-erase markers. Go get a BOX (no, not just a single pad) of sticky-notes. As you analyze your model, you will make table headers on your white board. You add "sample" data to the sticky notes to "populate" the tables. You will reach a point where you think you are done. So start drawing lines that represent relationships.

Sanity-check the tables as you go along. Here is where a normalization rule comes into play that makes a very PRACTICAL test of "purity" for each table. You should identify some type of key for every table. Keys will be primary for every independent table but you might have all foreign keys or a mix of foreign and primary keys for dependent tables. The table is "pure" or "normalized" when everything in the table depends on all of the table's keys and nothing else. Let's try an example:

A store is a potential object for which a separate table is needed. In that store table, the store ID number is the (obvious) PK.

A product is a potential object for which a separate table is needed. In that product table, the product ID number (your inventory control number) is an obvious PK.

The day's detailed sales receipts for a store are different. Why? Because they contain store information but they also contain DATE information and product information. The data can't go into the store table because that table's key is only the store ID. Putting date-related data in the store table would mean that some records depend on something other than the PK of the table. The same argument works for why you can't put that in the product table. But this is an insidious example that shows one more principle.

You might have several line items in a single sale, a single register transaction number. So if the key is the combination of store, product, and date, you would have several items with the same key yet they are different. This suggests another key of the register transaction number. This is becoming extraordinarily complex, isn't it? (That was rhetorical.) So what to do? At this point, you have to decide HOW you want your reports to look because the next answer guides how you store data. This is ALWAYS the case - the problem guides the design; otherwise you are letting Access run your business instead of merely track it.

NOW comes the fun part. You've just designed the static data. It is time to DO something with it. Now you say (for example), OK, how do I add data to this mess? And you look at the tables to realize that you might need to have data from two different tables represented in a single form. But that's OK because you can generate parent/child forms for cases of one/many relationships, or you can do JOIN queries where appropriate. It is OK to use queries in place of tables. In fact, preferable in many cases.

Somewhere along the way you will want reports. So examine the data in the tables and see from where your data must be recovered. Again, this will probably reveal JOIN queries that you will need to write.

When you have gone down this garden path for a while, you can post back with a new thread and more specific questions or continue this thread, your choice. After a while, a new and more specifically targeted thread is the forum's preference because it is easier to read. Some of these threads get really long and hard to wade through.