Product Offers Table

Sequel-Mart wants to start offering discount campaigns

We want to be able to:

• Give our offer an ID and a name (these must have values)
• Discount a range of products by between 0.01% and 99.99%
  • We need to provide a link to the Products table
• The offer should be open for a given time range in days

For example our first planned offer is:

• ID 1, with the name of 'Introductory Discount'
• This offer involves discounting our broad beans (product ID 65) by 5.5%
• The discount is applicable between 1st September 2021 and 30th September 2021

This specification might produce a table design like this:

CREATE TABLE "sequel-mart-schema"."Product_Offers" (
	offer_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	offer_name VARCHAR(50) NOT NULL,
	product_id INT NOT NULL DEFAULT 0,
	offer_discount_percentage NUMERIC(4,2),
	offer_start_date DATE,
	offer_end_date DATE
);

Make sure the schema is included in the name or it will be created in the 'Public' schema

If we run this script in a Query Tool Window, we should get:

• A message logging that a CREATE TABLE script was run
• A message logging whether it was successful
• How long it took to create if it was successful

Now if we right-click on the table list and click 'Refresh' the new table should appear

Notes (matching the column attributes with the specification above):

• offer_id
  • With only 129 products it will be a while before we issue more than 32,767 offers.
  • A SMALLINT will work in this case but it is considered good to standardise IDs where practical
  • Because of this, we can choose an INT
  • GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY means PostgreSQL will automatically add an ID as a record is added
• offer_name
  • It is likely that an offer will have fewer than 50 characters so we have chosen a VARCHAR(50)
  • The NOT NULL constraint without a default forces a user to enter an offer name before the record can be saved
• product_id
  • This should match the data type of the 'Products' table to speed up joined queries
• offer_discount
  • The scope specifies a maximum discount of 99.99% so we can apply a NUMERIC(4,2)
    • 4 digits, with the last 2 decimals
• offer_start_date & offer_end_date
  • Offers are valid between one point in time and another
  • Because no time is specified we are fine to use a DATE over a DATETIME