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

All we need is an easy explanation of the problem, so here it is.

I have these tables:

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

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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
)

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags:

Leave a Reply