 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / July 2006Table Quandry
I have a bit of a quandry with a table or tables as it may be. The first table is called MenuDetails, the fields are: TerminalID MenuID PriceID StartDay StartTime EndTime AfterMidnite Active the 2nd table is called MenuDetails...this has quite a few fields TerminalID DayID MenuID PriceID SectionID ItemID ItemPrice ItemPrinter ItemPrep Group GroupLevel1 GroupAction ModID ModPrice ModPrinter ModPrep SubGroup SubGroupLevel1 SubGroupAction SubModID SubModPrice SubModPrinter SubModPrep The problem is that the first table only has another 4 fields than the second table. Should I just can the idea of 2 tables? Will that be to many fields? The other problem is that the last field SubModID, depends on these fields because it can be different depending on whats in these fields....TerminalID, MenuID, DayID, PriceID, SectionID, ItemID, Group, ModID, SubGroup... Any help appreciated. Thanks DS > I have a bit of a quandry with a table or tables as it may be. > The first table is called MenuDetails, [quoted text clipped - 42 lines] > Thanks > DS OK, I thought maybe this would be better? Any comments welcome! Thanks DS TABLE1 TABLE2 TABLE3 TABLE4 TABLE5 TermID TermID TermID TermID TermID DayID DayID DayID DayID DayID MenuID MenuID MenuID MenuID MenuID PriceID PriceID PriceID PriceID PriceID MenCatID MenCatID MenCatID MenCatID MenCatID ItemID ItemID ItemID ItemID ItemID ItemPrice Group1ID GroupID GroupID GroupID ItemPrint Group1Level ModID ModID ModID ItemPrep Group1Action ModPrice SubGrpID SubGrpID ModPrinter SubGrpLevel SubModID ModPrep SubGrpAction SubMod$ SubModPr SubModPP I came up with this. Perhaps this is better. Any comments welcome. DS TABLE1 TerminalID DayID MenuID PriceID SectionID ItemID ItemPrice ItemPrinter ItemPrep TABLE2 TerminalID DayID MenuID PriceID SectionID ItemID GroupID GroupLevel GroupAction TABLE3 TerminalID DayID MenuID PriceID SectionID ItemID GroupID ModID ModPrice ModPrinter ModPrep TABLE4 TerminalID DayID MenuID PriceID SectionID ItemID GroupID ModID SubGroupID SubGroupLevel SubGroupAction TABLE5 TerminalID DayID MenuID PriceID SectionID ItemID GroupID ModID SubGroupID SubModID SubModPrice SubModPrinter SubModPrep DS, You normally don't have much choice regarding the design of your tables. The structure of the tables is pretty much dictated by the structure of your data. This means the nature of the data elements, and the real-life relationships between them. Your database will have as many tables, and as many fields in each table, as you need in order to correctly represent these realities. So far, it is quite difficult to see what your database is all about, based on the lists of fields you have given. I do note, though, that many of the same field names are repeated in these tables, which almost certainly means the design is not correct. Maybe a good idea if you could just give an outline description, in ordinary English, of the nature and purpose of the database, and of the meaning of the fields you have listed.  SignatureSteve Schapel, Microsoft Access MVP > I came up with this. Perhaps this is better. Any comments welcome. > DS [quoted text clipped - 61 lines] > SubModPrinter > SubModPrep > DS, > [quoted text clipped - 10 lines] > description, in ordinary English, of the nature and purpose of the > database, and of the meaning of the fields you have listed. Hi Steve, Thanks. Yes the first four fields are repeated because the fields following them need those four fields to identify the record. Basically it is this, I have Terminal that has Menus attached to them, these menus are attached to days of the week and each menu has a price level. Terminal Days Menus PriceID So you have a Bar Terminal that on Sunday has a Drink Menu attached to it. That Drink Menu has a Price Level (Needed so that I can have different prices on diffierent days for the same menu and the same terminal) example: on the Bar Terminal, on Monday, the same Drink Menu would have a different price. So as far as the other fields ItemID, etc each field that comes after it is dependent on the one before it. Is this clearer? Its hard, it'svery complicated. Any suggestions appreciated. Thanks DS DS, Well, you also have SectionID and ItemID replicated in the tables, and also some other fields such as GroupID, ModID in more than one table. What "entities" are these tables 1-5 meant to represent? Maybve giving them meaningful names instead of Table1 etc might help with comprehension? Thanks a lot for the further explanation, but I'm afraid I still haven't grasped what you are trying to achieve. And just to clarify what you have revealed so far, I can understand the concept that each Terminal operates on a number of days (of the week), and that on each day, there may be more than one Menu operational, and depending on the day, each menu may have a different pricing. So this part of your data might look like this... Bar Sun Drink A Bar Sun Food A Bar Mon Drink B Bar Mon Food C Foo Sun Drink A ... etc Is that roughly it? Well, that combination of data should only appear in one table in the database. SignatureSteve Schapel, Microsoft Access MVP > Hi Steve, Thanks. Yes the first four fields are repeated because the > fields following them need those four fields to identify the record. [quoted text clipped - 18 lines] > DS > > DS, > [quoted text clipped - 19 lines] > Well, that combination of data should only appear in one table in the > database. Thanks Steve, partially there. Now magine if you will as you go down the line.. to each of these records you have this... Each Menu will also have a Menu Section, such as Entree, Sides, etc. Each of these Section will have a Menu Item, such as Steak, Pizza, etc. Each Menu Item will have a Modifier Group such as Toppings, Temps, etc. Each Modifier Group will have a Modifier, such as Pepperoni, Potato,etc Each Modifier will also have a Sub Group, such as Toppings, Extras, etc. And Each Sub Group will have a Sub Mod, such as Butter, etc. So that by the time you get down to that Sub Mod, its root goes all the way back to the Terminal. This scenerio allows you to have a Menu with different prices attached to different days and terminals without making a whole new menu and calling it something else. Such as BAR then renaming it because a few prices are different, BAR2. Thanks Steve I appreciate the time and input. DS DS, Ok, well here's a "first pass" top-of-the-head idea to show the kind of direction I would go with this... Table: Menus MenuID Terminal MenuName Table: MenuItems MenuItemID MenuID Section MenuItem Table: Modifiers ModifierID MemuItemID ModifierGroup ModifierSubGroup Table: Schedules ScheduleID MenuID DayOfWeek PriceLevel SignatureSteve Schapel, Microsoft Access MVP > Thanks Steve, partially there. Now magine if you will as you go down > the line.. to each of these records you have this... [quoted text clipped - 10 lines] > a whole new menu and calling it something else. Such as BAR then > renaming it because a few prices are different, BAR2. > DS, > [quoted text clipped - 23 lines] > DayOfWeek > PriceLevel Thanks Steve, I'll give it a try and see how the structure plays out. DS OK Steve how about this? TableMenuDetails KeyID TerminalID DayID MenuID PriceID TableSections KeyID SectionID TableItems KeyID SectionID ItemID ItemPrice ItemPrinter ItemPrep TableGroups KeyID SectionID ItemID GroupID GroupLevel GroupAction TableMods KeyID SectionID ItemID GroupID ModID ModPrice ModPrinter ModPrep TableSubGroup KeyID SectionID ItemID GroupID ModID SubGroupID SubGroupLevel SubGroupAction TableSubMods KeyID SectionID ItemID GroupD ModID SubGroupID SubModID SubModPrice SubModPrinter SubModPrep Thank You DS DS, Unless I am misunderstanding, it seems to me that you are working on some mistaken assumptions, as a result of which you are getting your design tangled and complicated. I'm not able at the moment to do a detailed analysis, but here's an example... TableMenuDetails KeyID - Ok, presume this is a primary key field for this table, probably an AutoNumber? Fine. DayID - Name of day of week - ok MenuID - where does this come from? I think you need a Menus table to define this. Do you have a fixed set of Menus, each of which is used at all the Terminals? Or does each Terminal have its own set on Menus? If the latter, then the Terminal is recorded with the MenuID in the Menus table, and as such it is not valid to have a TerminalID field in the TableMenuItems table. (By the way, what's with TerminalID? Doesn't each Terminal have a unique name? Why not use the TerminalName? Or is that what TerminalID means?) TableSections Your original description said that this refers to Entree, Sides, etc. So TableSections is just a lookup table to list these optionsa for the use of comboboxes for data entry etc? I presume. So, is SectionID the name of the Section, e.g. "Entree"? So when we see SectionID in the TableItems table, this means the data entered there will be the name of the Section (hint: the answer to this should be Yes :-) )? Ok, so what is the meaning and purpose of the KeyID field in the TableSections table? TableGroups Your original explanation told me that Groups refers to categories of Modifiers. It is not correct to have ItemID and SectionID in this table. The Item and Section are not information about the Group. It's the other way around. The Group is information about the Item. And the Section is also information about the Item. In fact, even that's not correct, as I understand it. The Group is information about the Modifier. You can have a Group field in the Modifier table, which I see you have. But it makes no sense at all to have a Item and Section fields in the Groups table, or the Modifiers table for that matter. This is all confused. Sorry for the incomplete rersponse, but hopefully that will help you take another step forward. SignatureSteve Schapel, Microsoft Access MVP > OK Steve how about this? > [quoted text clipped - 59 lines] > Thank You > DS First Thank you for your time and effort,its greatly appreciated. > DS, > [quoted text clipped - 6 lines] > KeyID - Ok, presume this is a primary key field for this table, probably > an AutoNumber? Fine. The KeyID is a unique number that would identify the terminal, day, menu and price all of which are primary keys except keyID. So this would be unique. > DayID - Name of day of week - ok > MenuID - where does this come from? I think you need a Menus table to [quoted text clipped - 3 lines] > table, and as such it is not valid to have a TerminalID field in the > TableMenuItems table. I have a Menus table and these menus can be attached to any terminal on any given day. > (By the way, what's with TerminalID? Doesn't each Terminal have a > unique name? Why not use the TerminalName? Or is that what TerminalID > means?) TerminalID is the name of the computer itself. > TableSections > Your original description said that this refers to Entree, Sides, etc. [quoted text clipped - 4 lines] > the Section (hint: the answer to this should be Yes :-) )? Ok, so what > is the meaning and purpose of the KeyID field in the TableSections table? Tablesections are Entree, etc. > TableGroups > Your original explanation told me that Groups refers to categories of [quoted text clipped - 7 lines] > fields in the Groups table, or the Modifiers table for that matter. This > is all confused. The group is a catagory of modifier, Each group can be attached to a different item in a different section, on a different menu, on a different terminal. I figured I'd use the KeyID to combine the Terminal, Day, Menu, Price field so that its handier. I know its rather complicated. The problem is that it's vey deep level wise and the bottom of the level is dependent on the top. > Sorry for the incomplete rersponse, but hopefully that will help you > take another step forward. Once again Thank you, Your help is greatly appreciated on a complex matter. DS |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.