English to Tables

[DS]

I have this scenario worked out that I need to make into tables.
Any suggestions Welcome.

Each Table (Restaurant Table, not Access Table)
Can have several Checks
Each Check has One Server
Each Check has Several Items
Each Check has One Refund
Each Check has One Gratuity
Each Check has One Service Charge
Each Check has One Discount
Each Check has One Tip
Each Check has One Void

Each Item has Several Modifiers
Each Modifier has Several Sub-Modifiers
Each Item has One Void
Each Item has One Discount

Thank You,
DS

 

[Mike Revis]

DS
What is the distinction between a gratuity and a tip? Just curious, I used
to work in the hospitality industry.

Just off the top of my head it seems you have the following tables. Access
tables, not restaurant tables .

PK= primary key
FK = foreign key

tblTable - one to many with check
tableID-PK
tablenumber
other stuff about the table

tblCheck - one to many with item
tableID - FK
CheckID - PK
CheckNumber
other stuff on the check

tblItem - one to many with modifier
CheckID - FK
ItemID - PK
other stuff about item

tblModifier - one to many with submodifier
ItemID - FK
ModifierID - PK
other stuff about modifier

tblsubModifier -
ModifierID - FK
submodifierID - PK
other stuff about submodifier

Mike

 

[DS]

DS
What is the distinction between a gratuity and a tip? Just curious, I used
to work in the hospitality industry.

Just off the top of my head it seems you have the following tables. Access
tables, not restaurant tables .

PK= primary key
FK = foreign key

tblTable - one to many with check
tableID-PK
tablenumber
other stuff about the table

tblCheck - one to many with item
tableID - FK
CheckID - PK
CheckNumber
other stuff on the check

tblItem - one to many with modifier
CheckID - FK
ItemID - PK
other stuff about item

tblModifier - one to many with submodifier
ItemID - FK
ModifierID - PK
other stuff about modifier

tblsubModifier -
ModifierID - FK
submodifierID - PK
other stuff about submodifier

Mike

Thanks, That looks pretty good, have to make a change though, an item on
one check might have different modifiers,submodifiers than an Item on
another check...so.

tblModifier - one to many with submodifier
CheckID - FK
ItemID - FK
ModifierID - PK
other stuff about modifier

tblsubModifier -
CheckID - FK
ItemID - FK
ModifierID - FK
submodifierID - PK
other stuff about submodifier

So does this still work? BTW a Tip is what the customer elects to give
and a Gratuity is the Amount the restaurant decides to add.

Thank you, I appreciate the response.
DS

 

[Mike Revis]

DS
I did make a few assumptions.

Presumably a check is a unique item. Each item on that check only applies to
that check and no other checks.

You can have the same item appearing on multiple checks but each check is
unique and there is no relationship between the checks and therefore no
relationship between an item on one check and the same item on a different
check.

If anything on one check applies to another check then you have a whole
different scenario going on.

You base your tables on unique items. Or "entities" as they're called in the
db world.

*My Presumptions*

A table is unique.

A check is unique to a table. No other tables will have that check.

An item on *a* check is unique to that check and no other checks. Some items
on one check might appear on other checks but that is ok because each check
is unique.

A modifier for an item is unique to one item on one check at one table. You
might have the same modifier on multiple checks but there is no relationship
between the checks.

A submodifier is unique to one modifier on one item on one check at one
table.

If I misunderstand your intent post back.

Mike

 

[DS]

DS
I did make a few assumptions.

Presumably a check is a unique item. Each item on that check only applies to
that check and no other checks.

You can have the same item appearing on multiple checks but each check is
unique and there is no relationship between the checks and therefore no
relationship between an item on one check and the same item on a different
check.

If anything on one check applies to another check then you have a whole
different scenario going on.

You base your tables on unique items. Or "entities" as they're called in the
db world.

*My Presumptions*

A table is unique.

A check is unique to a table. No other tables will have that check.

An item on *a* check is unique to that check and no other checks. Some items
on one check might appear on other checks but that is ok because each check
is unique.

A modifier for an item is unique to one item on one check at one table. You
might have the same modifier on multiple checks but there is no relationship
between the checks.

A submodifier is unique to one modifier on one item on one check at one
table.

If I misunderstand your intent post back.

Mike

This all sounds good, makes a lot of sense, I'll try it out and get back
to you. I could be wrong! Wont be the first time, Wont be the last!
Thanks
DS