CODEWITHSUNDEEP
databasedatabase-designms-access-2007
broke
broke

Reputation: 8302

Help with table relationships

I need a little help designing a database that a bar would use for drinks. My app handles drink orders in 2 ways: A customer can order drinks and pay right away, or they can start a tab. This is my table relationship so far:

Table Order:        Table Tabs:
------------        ------------
[PK] OrderId    - 1 [PK] TabId
ItemName       /    TabName
QtyOrdered    /     Total
TabId       *-      PaymentType
                    Archive

Its setup so 1 tab can have many drinks on it. Thats great for tabs, but customers can choose not to setup a tab. My question is, how can i modify these two tables to support that? Or do I have to create a new table?

Thanks in advance.

Upvotes: 0

Views: 69

Answers (2)

Tim
Tim

Reputation: 5421

Your "item" is the order line-item. 

       "My brother and I will each have a Guiness and his wife would like a gin-and-tonic."

There are three (EDIT: items, but two) line-items on that order. To identify them as part of the same order [if you should need to do so], you would need two tables:

         the OrderHeader
         (orderheaderid, orderdatetime, customerid)

and 

        the OrderDetail 
        (id, orderheaderid, drinkid, howmany, extendedprice, paymentamountapplied).

If you do not need to identify the drinks as belonging to the same order, you could abandon the two-table structure, but then you could have only one kind of drink per order.

              ORDER
              orderid, orderdatetime, customerid, drinkid, howmany, extendedprice, paymentapplied.

Extended price is howmany * the drink's price at time of the order. You store this value in your order table so you can change the price of the drink in the DRINKS table without affecting the tab amount owed (in case the tab lifetime is longer than a day -- i.e. customers can run a monthly or quarterly tab).

HowMany can default to 1. The amount owed on a drink line-item is (extendedprice - paymentamountapplied). PaymentAmountApplied defaults to 0 (i.e. default is to run a tab).

If you do not need to track the kind of drink being ordered (i.e. you don't care to use your database to discover that on Tuesday nights you sell a lot more gins-and-tonic than Guinesses, for some reason):

             ORDER
             orderid, orderdatetime, customerid, ordertotal, paymentapplied.

Your barkeep would simply enter the total amount of the order, calculated outside your system, without reference to a DRINKS table in the database -- maybe the barkeep would look at a chalkboard on the wall.

Upvotes: 1

Tim
Tim

Reputation: 5421

You need a CUSTOMERS table, a DRINKS table, an ORDERSHEADER table, an ORDERDETAIL table (each drink on the tab is a line-item). You could/should have a separate PAYMENTS table. And you would allocate payments to the line-items of the ORDERDETAIL (your tab). The "tab" is the set of all line-items on the order(s) that have no payment applied to them.

Upvotes: 1

Related Questions