Distinguish between "take-away orders" and "orders for a table"

Question

I have these tables:

I have to distinguish an order: it can be an order to a specific table or a take-away order. If it's an order to a specific table, I just need to store table number and the number of people that are on that table currently; If it is a take-away order, I just need to store the name of the person who "booked" the order (they told me they don't want surname and the time, just the name).

How can I do that? I thought to add 3 columns to Orders table, table_number (INT), n_people_in_table (INT), take_away_name (VARCHAR), so if the order is for a table I would populate the table_number and n_people_in_table fields, and the take_away_name would be be NULL. Otherwise, if the order is a take-away order, I would populate take_away_name field; table_number and n_people_in_table fields would be NULL.

But I don't like this solution very much and I don't think it is an elegant way. Which is the best way to do that?

score 1 · Accepted Answer · edited Aug 02 '22 at 21:51

The isn't the best way of doing it. There are different pro and cons in each solution. And this can change in future.

I would use discrete tables for each order type, with referential integrity:

CREATE TABLE Orders
(
      order_id          INT      not null
        PRIMARY KEY
    , order_date        DATETIME not null
)
CREATE TABLE TableOrders 
(
      order_id          INT      not null
        FOREIGN KEY 
        REFERENCES 
        Orders(order_id)
    , table_number      INT      not null
    , n_people_in_table INT      not null
)
CREATE TABLE TakeAwayOrders 
(
      order_id          INT      not null
        FOREIGN KEY 
        REFERENCES 
        Orders(order_id)
    , take_away_name    VARCHAR(30) not null
)

In future you can add other types of orders; an example:

CREATE TABLE ShippedOrders 
(
      order_id               INT not null
        FOREIGN KEY 
        REFERENCES 
        Orders(order_id)
    , name                   VARCHAR(100) not null
    , address_id             INT not null
    , delivery_timerange_id  INT not null
)

Distinguish between "take-away orders" and "orders for a table"

1 Answers1