Proposals for fixing the table data

Here is our existing table:

name	description	price	count	weight	dimensions
Anne	Cork Board	8.29	1	1.2	17x0.8x23
John	Fuzzy Socks	2.50	5	0.1	9x7x0.5
Ken	Fedora	16.99	2	0.1	9x9x4
Anne	Printer	129.99	1	9.5	15.4x11.8x5.7
Anne	Fuzzy Socks	2.50	3	0.1	9x7x0.5
Ken	Printer	129.99	1	9.5	15.4x11.8x5.7
John	Lamp	79.99	1	2.57	10.5x7x20.5
Ken	Fuzzy Socks	2.50	1	0.1	9x7x0.5
Anne	Gift Card	40.00	1	0	0
Ken	Gift Card	20.00	2	0	0

The table data organization has two main problems:

• It repeats information about catalog items in multiple rows of the orders table
• It has multiple rows for each person, even though the tasks mostly process orders for a single person.

Here are two concrete proposals for organizing the data which address one or both of these problems:

Proposal 1: Linking the Tables

The current orders table repeats the price and weight columns from the catalog. Instead, it would be better to leave that information in the catalog table, and have a way to reference into particular rows of the catalog table from the orders table.

Concretely: replace the description, price, andweight columns in the orders table with an item-id column. Then, when a program needs the price of an item, the item-id can be used to find the appropriate row in the catalog.

New order table (first two rows):

name	item-id	count
Anne	7	1
John	6	5

These item-ids match the contents in the first column of the catalog, as shown in the Google Sheet.

Proposal 2: Create Datatypes

Dispense with the tables entirely, and instead use three datatypes:

• A datatype called ItemData for data about catalog items and gift cards. For catalog items, the components are the description, price, weight, and dimensions. For gift-cards, the only component is the amount stored on the card.

• A datatype called Order which has two components: an ItemData and the count of that item that someone wants to order (there would be one Order value for each row of the original orders table)

• A datatype called UserOrder which has two components: the customer’s name, and a list of their Order.