Database Administration
database-design
Updated Mon, 26 Sep 2022 22:46:07 GMT

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


I have these tables:

enter image description here

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?

Solution

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
)





External Links

External links referenced by this document:

References