Table Quandry

[DS]

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

 

[DS]

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

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

 

[DS]

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

 

[Steve Schapel]

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.

 

[DS]

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.

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

 

[Steve Schapel]

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.

 

[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.

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

 

[Steve Schapel]

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

 

[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

Thanks Steve, I'll give it a try and see how the structure plays out.
DS

 

[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

 

[Steve Schapel]

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.

 

[DS]

Steve Schapel wrote:
First Thank you for your time and effort,its greatly appreciated.

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.

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
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.

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.
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? Tablesections are Entree, etc.

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.

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