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,
take_away_name (VARCHAR), so if the order is for a table I would populate the
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
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?
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 referenced by this document: