Database

From the mock-up of the purchase order we can see that we will need two main tables to record the purchase order, a purchase order header table and a purchase order lines table.  In addition we will need supporting tables for:

  • Supplier
  • Product
  • Tax Rates

An initial analysis suggests the following tables

Purchase Order Header

  • POHeaderId
  • POHeaderOrderNumber
  • POHeaderOrderDate
  • POHeaderSupplierId
  • POHeaderSupplierAddress1
  • POHeaderSupplierAddress2
  • POHeaderSupplierAddress3
  • POHeaderSupplierPostCode
  • POHeaderSupplierEmail
  • POHeaderRequestedBy
  • POHeaderIsArchived

The reason that I have included details of the supplier's address and email is to allow the user flexibility when entering the order. It can sometimes be necessary to use a different address, or email, from those held on the supplier's record and it is much easier to overwrite the defaults than either add a new supplier record with a different address, or amend an existing record.  (I am aiming to keep things simple, so am not getting into the realms of multiple addresses and/or emails for a each supplier!) 

Purchase Order Line

  • POLineId
  • POHeaderId
  • POLineProductId
  • POLineProductDescription
  • POLineProductQuantity
  • POLineProductUnitPrice
  • POLineTaxRate

For the same reasons as for suppliers, I will allow users to over-write product descriptions and unit price.  To save users the task or entering every conceivable product, some default products could be used and the description and unit price simply over-written at the point of data entry.

Supplier

  • SupplierId
  • SupplierAddress1
  • SupplierAddress2
  • SupplierAddress3
  • SupplierPostCode
  • SupplierEmail
  • SupplierIsArchived

Product

  • ProductId
  • ProductCode
  • ProductDescription
  • ProductUnitPrice
  • ProductSupplierId
  • ProductIsArchived

Tax Rate

  • TaxId
  • TaxDescription
  • TaxRate
  • TaxIsArchived

Archived Data

I don't intend that records should be deleted, but instead marked as 'archived'; for most forms archived records will be filtered out, but later in the project we might give the user the option of viewing archived data.

Adding the SQL Database

  • Open Microsoft SQL Management Studio and connect to a Database Engine. (If using MS SQL Express, just accept the defaults.)
  • Object Explorer should be visible, if not select View > Object Explorer from the menu.
  • Right-click on Databases and select New Database...
  • Enter a name for the database in the New Database pop-up form - I suggest 'PurchaseOrders'. Leave all other entries as defaults and click 'OK'.
  • Having created the database, highlight it, right-click and select 'New Query' or select 'New Query' from the toolbar.
  • Copy the code from below (click the 'Copy' button) and paste into the 'New Query' tab in SQL.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[POHeader](
	[POHeaderID] [int] IDENTITY(1,1) NOT NULL,
	[POHeaderOrderNumber] [int] NOT NULL,
	[POHeaderOrderDate] [date] NOT NULL,
	[POHeaderSupplierID] [int] NOT NULL,
	[POHeaderSupplierAddress1] [nvarchar](50) NULL,
	[POHeaderSupplierAddress2] [nvarchar](50) NULL,
	[POHeaderSupplierAddress3] [nvarchar](50) NULL,
	[POHeaderSupplierPostCode] [nvarchar](10) NULL,
	[POHeaderSupplierEmail] [nvarchar](256) NULL,
	[POHeaderRequestedBy] [nvarchar](450) NULL,
	[POHeaderIsArchived] [bit] NOT NULL,
 CONSTRAINT [PK_POHeader] PRIMARY KEY CLUSTERED 
(
	[POHeaderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[POLine](
	[POLineID] [int] IDENTITY(1,1) NOT NULL,
	[POLineHeaderID] [int] NOT NULL,
	[POLineProductID] [int] NOT NULL,
	[POLineProductDescription] [nvarchar](50) NOT NULL,
	[POLineProductQuantity] [decimal](9, 3) NOT NULL,
	[POLineProductUnitPrice] [money] NOT NULL,
	[POLineTaxRate] [decimal](6, 4) NOT NULL,
 CONSTRAINT [PK_POLine] PRIMARY KEY CLUSTERED 
(
	[POLineID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Product](
	[ProductID] [int] IDENTITY(1,1) NOT NULL,
	[ProductCode] [nvarchar](25) NOT NULL,
	[ProductDescription] [nvarchar](50) NOT NULL,
	[ProductUnitPrice] [money] NOT NULL,
	[ProductSupplierID] [int] NULL,
	[ProductIsArchived] [bit] NOT NULL,
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
(
	[ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Supplier](
	[SupplierID] [int] IDENTITY(1,1) NOT NULL,
	[SupplierName] [nvarchar](50) NOT NULL,
	[SupplierAddress1] [nvarchar](50) NULL,
	[SupplierAddress2] [nvarchar](50) NULL,
	[SupplierAddress3] [nvarchar](50) NULL,
	[SupplierPostCode] [nvarchar](10) NULL,
	[SupplierEmail] [nvarchar](256) NULL,
	[SupplierIsArchived] [bit] NOT NULL,
 CONSTRAINT [PK_Supplier] PRIMARY KEY CLUSTERED 
(
	[SupplierID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tax](
	[TaxID] [int] IDENTITY(1,1) NOT NULL,
	[TaxDescription] [nvarchar](50) NOT NULL,
	[TaxRate] [decimal](6, 4) NOT NULL,
	[TaxIsArchived] [bit] NOT NULL,
 CONSTRAINT [PK_Tax] PRIMARY KEY CLUSTERED 
(
	[TaxID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[POHeader] ADD  CONSTRAINT [DF_POHeader_POHeaderOrderNumber]  DEFAULT ((0)) FOR [POHeaderOrderNumber]
GO
ALTER TABLE [dbo].[POHeader] ADD  CONSTRAINT [DF_POHeader_POHeaderIsArchived]  DEFAULT ((0)) FOR [POHeaderIsArchived]
GO
ALTER TABLE [dbo].[POLine] ADD  CONSTRAINT [DF_POLine_POLineProductQuantity]  DEFAULT ((0)) FOR [POLineProductQuantity]
GO
ALTER TABLE [dbo].[POLine] ADD  CONSTRAINT [DF_POLine_POLineProductUnitPrice]  DEFAULT ((0)) FOR [POLineProductUnitPrice]
GO
ALTER TABLE [dbo].[POLine] ADD  CONSTRAINT [DF_POLine_POLineTaxRate]  DEFAULT ((0)) FOR [POLineTaxRate]
GO
ALTER TABLE [dbo].[Product] ADD  CONSTRAINT [DF_Product_ProductUnitPrice]  DEFAULT ((0)) FOR [ProductUnitPrice]
GO
ALTER TABLE [dbo].[Product] ADD  CONSTRAINT [DF_Product_ProductInactive]  DEFAULT ((0)) FOR [ProductIsArchived]
GO
ALTER TABLE [dbo].[Supplier] ADD  CONSTRAINT [DF_Supplier_SupplierInactive]  DEFAULT ((0)) FOR [SupplierIsArchived]
GO
ALTER TABLE [dbo].[Tax] ADD  CONSTRAINT [DF_Tax_TaxRate]  DEFAULT ((0)) FOR [TaxRate]
GO
ALTER TABLE [dbo].[Tax] ADD  CONSTRAINT [DF_Tax_TaxIsArchived]  DEFAULT ((0)) FOR [TaxIsArchived]
GO
ALTER TABLE [dbo].[POHeader]  WITH CHECK ADD  CONSTRAINT [FK_POHeader_Supplier] FOREIGN KEY([POHeaderSupplierID])
REFERENCES [dbo].[Supplier] ([SupplierID])
GO
ALTER TABLE [dbo].[POHeader] CHECK CONSTRAINT [FK_POHeader_Supplier]
GO
ALTER TABLE [dbo].[POLine]  WITH CHECK ADD  CONSTRAINT [FK_POLine_POHeader] FOREIGN KEY([POLineHeaderID])
REFERENCES [dbo].[POHeader] ([POHeaderID])
GO
ALTER TABLE [dbo].[POLine] CHECK CONSTRAINT [FK_POLine_POHeader]
GO
ALTER TABLE [dbo].[POLine]  WITH CHECK ADD  CONSTRAINT [FK_POLine_Product] FOREIGN KEY([POLineProductID])
REFERENCES [dbo].[Product] ([ProductID])
GO
ALTER TABLE [dbo].[POLine] CHECK CONSTRAINT [FK_POLine_Product]
GO
ALTER TABLE [dbo].[Product]  WITH CHECK ADD  CONSTRAINT [FK_Product_Supplier] FOREIGN KEY([ProductSupplierID])
REFERENCES [dbo].[Supplier] ([SupplierID])
GO
ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_Supplier]
GO
  • Make sure the 'PurchaseOrders' database is selected and click the 'Execute' button on the toolbar to run the code. You should see a confirmation message to say the command has completed successfully.

The 'CREATE TABLE' sections create the tables, whilst the 'ALTER TABLE' sections add defaults to data columns that cannot be null, and also creates the foreign keys where necessary.

  • Close the 'New Query' tab; there is no need to save the query.
  • If you wish, you can expand 'PurchaseOrders' and then Tables to show the new tables. Right-clicking on a table and selecting 'Design' will open a pane showing the column names and their attributes.

The database is now created and SQL Management Studio may be closed.

YouTube Videos

Blazor Project - Purchase Orders - Part 2 - short version : This video shows how to add the SQL database and create the tables using the SQL script shown above.

Blazor Project - Purchase Orders - Part 2 - long version : This video shows how to add the SQL database, and then shows how to add the table definitions manually.

Powered by Publii