?junction table? Sales_Items, Item_Types, and Menus: How to put this together? (1 Viewer)

Okay, so time for a more specific question related to my last post:

The db has several tables for different type of sales items:
tbl_FOOD
tbl_BEV
etc.

These tables have fields:
FOOD_ID
BEV_ID
etc.

as the PK and then fields:
FOOD_NAME
BEV_NAME

for a text description of the item.

I also have:
tbl_ITEM_TYPE

which just assigns a value to each type with fields:
ITEM_TYPE_ID
ITEM_TYPE

so for example you have ITEM_TYPE_ID = 1, ITEM_TYPE = Food.

Now I have:
tbl_SALES_ITEM

that I want to incorporate:
ITEM_TYPE and related ID (FOOD_ID, BEV_ID) from the specific table

The FOOD_ID / BEV_ID is used as a FK as SALES_ITEM_ID in tbl_SALES_ITEM.
(ex. In tbl_FOOD, FOOD_ID = 1, FOOD_NAME = Hamburger. So in tbl_SALES_ITEMS I have SALES_ITEM_ID = 1, ITEM_TYPE_ID = 1, SALES_ITEM = 1, This is a Hamburger which has an ITEM_TYPE of FOOD.)

I am doing all this because I want to keep food and beverages in separate tables, but I want users to be able to see a "menu" and sort their view by Item Type (food / bev). My gut wants to do this by throwing all my menu items together in one table and then give that table a separate column labeled ITEMTYPE and then use combo boxes, listboxes and IF...THEN statements in VBA to filter things out for the user to see... BUT, as per my previous posting in this room, I am really, really new to the idea of normalization (by new, I mean about 3 days into hearing the word), and I now think it is better to keep the different TYPES of items in different tables, have a different table for ITEM_TYPES and then relate those 2 tables together to create a sales item. But how?

Sorry if this is rambling and confusing, but my mind is rambling and confused. I would appreciate any help / suggestions.

Thanks in advance.

MR_G

I'm even newer to normalization (3 hours), but I think you don't want people into your tables. You can tie all of those together with a form, and use combo boxes to make the filters you mentioned, non?. Wouldn't the Item Type Id just be another FK in Sales_Item_ID?

Maybe I'm too new to be answering this.

visacrum said:

I'm even newer to normalization (3 hours), but I think you don't want people into your tables. You can tie all of those together with a form, and use combo boxes to make the filters you mentioned, non?. Wouldn't the Item Type Id just be another FK in Sales_Item_ID?

Maybe I'm too new to be answering this.

Click to expand...

You may be new to all this but I agree with you that tables should be hidden from users who should only see Forms and Reports.

Oh wait. Also, since you're adding an Item Type table, couldn't you just get rid of one of your first 2 tables? For example, you'd just have tbl_Items, and in it would be your PK and description (let's say item 1 is hamburger, item 2 is soda). Now you've got your item type table, as a one to many. So Item 1 ties with Item Type 1 (call it food), and Item 2 ties with Item Type 2 (call it beverage, or soft drink, or however you split your categories down). Then you've eliminated an entire table.

Sorry, I was not so clear in the last post. The user will most definitely be using forms to input the data, but now I see what you all are saying, i think. I agree, i really WANT to get rid of the FOOD and BEV tables, but in reaching 3nf, if I have tbl_BEV with fields, BEV_TYPE (coffee, beer, wine, etc.), ITEM_TYPE (beverage) and tbl_FOOD that does not include field BEV_TYPE, then when I combined the 2 tables into, say, tbl_ITEMS, I have field BEV_TYPE which is not related to any of the FOOD items in the table, therefore, FOOD should have its own table. Is this following normalization logic? or I am just making this harder than it should be?

I will put this out there for anyone else that might be in my situation. I am okay with VBA and SQL, which I think, is why I have never thought to normalize before. In a form, I can direct what the user is seeing, setting rowsources and bound columns on the fly so that the user only sees and inputs what she / he need to. The problem is, in the past, I would just dump it all into a flat file (or several non-normalized or 1NF type tables) and then laboriously get the data out using weird SQL queries that did the job. As I explained in my previous post, this was fine with a couple thousand datapoints, but if we are talking a couple hundred thousand, Access just craps out.

So for as much as I want to just kill the FOOD / BEV tables, I feel that that is just backtracking to a "flatter" file.

Is my thinking on this correct?

MR_G said:

...if I have tbl_BEV with fields, BEV_TYPE (coffee, beer, wine, etc.), ITEM_TYPE (beverage) and tbl_FOOD that does not include field BEV_TYPE, then when I combined the 2 tables into, say, tbl_ITEMS, I have field BEV_TYPE which is not related to any of the FOOD items in the table, therefore, FOOD should have its own table...
Is my thinking on this correct?

Click to expand...

The tbl_ITEMS would have, say, 3 fields. PK, FK (From tbl_ITEM TYPE), Name.

Try to not think of your objects as food and drink, think of them as objects which are CATEGORIZED as food and drink from another table.

So you'd have Item 1, Hamburger, the FK for Food. Item 2, Wine, the FK for wine. And so on.

If you're looking to delineate your beverages into what TYPE of beverage they are, break those down into further types. You could have Bev Alcohol, Bev Nonalcohol. You could further break food down too. Appetizer, Main, Dessert. But then you might need a fourth table, but that may be overpurification. But but, since food types shouldn't ever need to be deleted (hamburgers come and go, but Dessert is eternal), you shouldn't have any anomalies. But if you're just concerned that a Beverage is a Beverage and that's it, then think of what you now call Bev_Type and Food_Type as just 1 field called something like Name or Desc.

I'm sure Doc will be along shortly to poke all kinds of holes into this, but that's my understanding so far of normalization.

MR_G said:

or I am just making this harder than it should be?

Click to expand...

Yes.

MR_G said:

I will put this out there for anyone else that might be in my situation. I am okay with VBA and SQL, which I think, is why I have never thought to normalize before.

Click to expand...

It's cool you noticed that. I can program up a storm in most languages but find that with a properly normalized database using Access, I can do almost everything required in SQL without the use of VBA. It always amazes me that so many admitted "noobs" on these forums ask advanced VBA programming questions. It just isn't required.

I'll give you a couple of pointers on your design: You shouldn't break beverages and food items out into separate tables. Keep it object oriented.

MenuItems:
ID (PK Autonumber)
MenuItem (text)
MenuItemDescription
FromDate
ToDate
Other stuff (not pricing or categories)

ItemCategoryTypes:
ID (PK Autonumber)
CategoryType (text)
FromDate
ToDate
etc.

MenuItemCategories:
ID (PK Autonumber - just in case)
MenuItemID (FK)
ItemCategoryTypeID (FK)
FromDate
ToDate
etc.

MenuItemPrices:
ID (PK Autonumber - just in case)
MenuItemID (FK)
Price
FromDate
ToDate
etc.

And so on.

Of course, there are some really neat things you could do with each table to enhance design (as opposed to analysis) components.

MR_G said:

The problem is, in the past, I would just dump it all into a flat file (or several non-normalized or 1NF type tables) and then laboriously get the data out using weird SQL queries that did the job. As I explained in my previous post, this was fine with a couple thousand datapoints, but if we are talking a couple hundred thousand, Access just craps out.

So for as much as I want to just kill the FOOD / BEV tables, I feel that that is just backtracking to a "flatter" file.

Is my thinking on this correct?

Click to expand...

It wouldn't be backtracking. You'd just be connecting everything with keys, and all of that should be able to be displayed in a form. So the thing looks and sees Hamburger is Item 1 Food Type 1, and a form can draw out the fact For item one, this table says it's Hamburger and this table says it's food, so you see 'Hamburger. Food.' Normalization says you shouldn't have duplicate rows, duplicate columns, or any null fields. So you'd mash Food_Desc and Item_Desc into just Desc. You wouldn't need several tables for your several food types, because you're identifying its type with the FoodType foreign key, and your data should be more manageable - less tables of the same kind of data.

The more you need to drill it down, the more tables you'd need. Take ingredients. If you need to take those into account, there'd be a whole separate other table, probably on a many-to-many with your items. Think of cake ingredients, then french toast ingredients. There's your sugar, and eggs...I think you get the point. I mean, maybe you want to display what an item is made up of in case of food allergies or something. Then again, I don't know what capacity this thing is being used in.

Thx Georgedwilkinson and visacrum. George, thx for the ex, i think i see the format now. And thx for the feedback visacrum, its easy for me to make things harder than they need to be. Appreciate all the help!

Sorry folk, hate to be such an idiot, but I am still perplexed by this:

In the Georgedwilkonson post, I see how I can create MENUITEMS with associated types. But what I don't see, is how to further divide some of those types into subtypes. Just to stick with my example. Maybe a Hamburger gets a ItemType of 1 = Food, a Budweiser gets an ItemType of 2 = Beverage, and a Yellow Tail gets an ItemType of 2 = Beverage. But then I would like to break down ONLY the 2's (Beverages) into say 1 = Beer, 2 = Wine. This is tripping me up, because I can't associate Beer and Wine with the menu items because then I am required to tell Access whether a Hamburger is Beer or Wine, which it is obviously neither. Thanks everyone for being so patient.

MR_G

There are 3 possible solutions:
One is sometimes called an elephant's ear. In this method, you put an FK in your lookup table that references the PK in the same table. In other words, a row in your table can have a parent row in the same table:
ItemCategoryTypes:
ID (PK Autonumber)
CategoryType (text)
ParentType (FK from the same table Long Integer)
FromDate
ToDate
etc.

A second method is to have a different lookup table for sub-categories. You should do this if your analysis suggests this is the best method. And sometimes it's the right thing to do. Please understand that when I answered you earlier, I was trying to give the simplest answer that I thought you'd understand based on what little I understand of your problem domain. I hardly ever create even a simple solution that doesn't have gobs of tables in it. But that's just me. You should do what your business defines as the rules for this system.

A third method is using a M:M configuration, an additional junction table, and yet another lookup table. I'll be happy to elaborate on this if the first or second method doesn't work for you.

Hey George,

Can you elaborate on the Elephants ear solution?