Some Initial Setup
Before I start answering requests from DK Travel, I need to set up an initial customer database and due to GDPR cannot use real data. I have downloaded some English first names from the internet, I have randomly added them to real surnames from a clients database and used a random address generator online to produce addresses. I now have 1000 customers and have added them into a SQL Server 2019 database table called tblCustomers. I added an identity column as primary key and also added a town column and using T-SQL split out the town from the end of the address line. The T-SQL for this is below.
ALTER TABLE [DKTravel].[dbo].[addresses] ADD Town VARCHAR(100)
UPDATE [DKTravel].[dbo].[addresses] SET Town = TRIM(RIGHT(Address,CHARINDEX(',',REVERSE(Address))-1))
ALTER TABLE [DKTravel].[dbo].[addresses] ADD ID INT IDENTITY(10000,1) PRIMARY KEY
EXEC sp_rename '[addresses]', 'tblCustomers' The next step is to create my resorts table
CREATE TABLE tblResort (ID INT IDENTITY(1,1) PRIMARY KEY, Resort VARCHAR(100), PostCode VARCHAR(10)) and populate as
INSERT INTO tblResort (Resort) VALUES ('Scarborough')
INSERT INTO tblResort (Resort) VALUES ('Keswick')
INSERT INTO tblResort (Resort) VALUES ('Bamburgh')
INSERT INTO tblResort (Resort) VALUES ('Edinburgh')
INSERT INTO tblResort (Resort) VALUES ('Great Yarmouth') The next step is to create a bookings table.
CREATE TABLE tblBookings (
ID INT IDENTITY(1,1) PRIMARY KEY,
CustomerID INT CONSTRAINT FK_BookingToCustomer FOREIGN KEY REFERENCES
tblCustomers(ID),
BookingDate DATETIME,
BookingAmount MONEY,
PassengerCount SMALLINT,
DepartDate DATETIME,
ReturnDate DATETIME,
LeadTime INT,
ResortID INT CONSTRAINT FK_BookingToResort FOREIGN KEY REFERENCES
tblResort(ID)
)
I need some way of generating random bookings for a selection of customers across the date range DK Travel has been in business, lets say since 2018. This can be done in T-SQL or Python or even Python in and SQL Server Procedure. I will leave that to the next blog post.

Comments