Design a relational database for a specified user need (P2)

This database is for an IT Support company who helps their clients with IT related issues. The database will need a ticket system where the client can make a ticket on the problem that they have which is assigned to an employee to solve.

Relationships

This relationship represents the connection between the tables. The ClientCompanyT has a one-to-one relationship with the client as each company is related to each individual client. The client can submit many tickets to our IT Support ticket system which is why ClientT has a one-to-many relationship with TicketT. There can only be one department code upon the many codes which can link to many different employees as each employee in the IT Support Company specialises in one department. For example, one employee might specialise in Internet Issues and if the client submits a ticket that has the department code related to internet issues, than that employee will be assigned to complete the ticket. Having said that, each individual employee has a one-to-many relationship with TicketT as the employee can be assigned to multiple tickets. Also, there only can be one department that a ticket is linked to so this means that it has to be a one-to-one relationship.

Tables

I will have five tables in my relational database which are the following:

ClientT

Field Name	Data Type	Field Size	Validation Rule
ClientID (PK)	AutoNumber	N/A	N/A
FirstName	Short Text	15	N/A
LastName	Short Text	15	N/A
Gender	Short Text	6	”Male” Or “Female”
CompanyID (FK)	Number	N/A	N/A
Phone	Short Text	11	N/A

ClientCompanyT

Field Name	Data Type	Field Size	Validation Rule
CompanyID (PK)	AutoNumber	N/A	N/A
CompanyName	Short Text	30	N/A
Address	Short Text	30	N/A
City	Short Text	20	N/A
County	Short Text	20	N/A
PostalCode	Short Text	8	N/A
Phone	Short Text	11	N/A
Email	Hyperlink	N/A	N/A
Website	Hyperlink	N/A	N/A

EmployeeT

Field Name	Data Type	Field Size	Validation Rule
EmployeeID (PK)	AutoNumber	N/A	N/A
FirstName	Short Text	15	N/A
LastName	Short Text	15	N/A
Gender	Short Text	6	”Male” Or “Female”
DOB	Date/Time	N/A	N/A
City	Short Text	20	N/A
County	Short Text	20	N/A
PostalCode	Short Text	8	N/A
Phone	Short Text	11	N/A
Email	Hyperlink	N/A	N/A
DepartmentCode (FK)	Short Text	5	>“IT000”
isWorking	Yes/No	N/A	N/A

DepartmentT

Field Name	Data Type	Field Size	Validation Rule
DepartmentCode (PK)	Short Text	5	>“IT000”
DepartmentName	Short Text	20	N/A

TicketT

Field Name	Data Type	Field Size	Validation Rule
TicketID (PK)	AutoNumber	N/A	N/A
ClientID (FK)	Number	N/A	N/A
DepartmentCode (FK)	Short Text	5	>“IT000”
Description	Short Text	255	N/A
DateCreated	Date/Time	N/A	Default Value = Date()
DateCompleted	Date/Time	N/A	N/A
EmployeeID (FK)	Number	N/A	N/A
isCompleted	Yes/No	N/A	N/A

Data Entry Forms

These are the layouts of the forms that I will create in the database for each table so that users can easily add, delete and save records without tampering with the database itself.