Database

Diving into this project felt like having enthusiasm of a hundred neophytes. I was eager to apply the powerful database concepts I had only recently learned in class and Matisse, the post-relational SQL database, seemed like the perfect tool to do just that.

Why Matisse?

Matisse, the post relational sql database. Isn't particually a new kid on the block but rather unfortunately its been discontinued due to lack of mass adoption of Object Oriented Database Management Systems. Which is unfortunate because matisse isn't just a pure OODBMS. According to them:

Designed to benefit both object developers and database administrators, Matisse combines native support for Object, XML and SQL within a single database.

Unlike traditional relational databases or even pure object-oriented database management systems (OODBMS), Matisse provides native support for:

• Objects: Directly storing and retrieving instances of classes from C#, Java, or Python.

• SQL: Running familiar relational queries.

• XML: Integrating hierarchical and semi-structured data when needed.

Key Strengths:

• Eliminates the need for ORMs: Your application’s classes can be directly mapped to the database schema.

• Strong type safety: Object relationships and constraints are embedded in the schema, promoting a clean domain model.

• Powerful indexing: Native indexing options on almost every entity make for efficient querying.

These are but a few of the main reasons I decided to use matisse for this project, it allows me to draw up one database schema that in itself maps to application models without having to write boilerplate code two times.

Database Schema

To have a general view of the schema without committing to a single design I started with Database Modeler a tool provided by

Employees & Departments

Employee

The central model representing workers in the organization.

• Attributes: Basic identification, contact, and employment details (e.g. HireDate, Salary, Commission, Photo).

• Relationships:

• MemberOf: Links an employee to their department.

• Head: One employee may head a department.
• Job: Each employee is assigned an Occupation.
• EmployeeRole: Permissions or access level abstraction.
• Credentials: One-to-one mapping to EmployeeLogin.
• HomeAddress: Optional residential link.

Indexes on EmployeeNumber, Firstname, and Surname help fast retrieval by key identity or name.

Department

Organizational unit with:

• A list of Members (employees).
• An optional DepartmentHead (employee).
• Indexed by a unique DepartmentNumber.

Authentication & Access Control

EmployeeLogin

Credentials stored separately with:

• Username (unique), Password, and LastLogin.
• Inverse relationship to a single Employee.

Role

Represents the permission level or functional group in the system.

• Roles have names, optional descriptions, and AccessLevel.
• Connected to many employees.

Jobs and Occupations

Occupation

Job titles or duties — e.g., "Technician", "Salesperson".

• Related to multiple employees.
• Indexed by Name for fast role-based lookups.

Clients & Addresses

Client

Represents a customer:

• Contact info (Email, Mobile) and metadata (RegistrationDate, DateOfBirth).

• Linked to:

• ClientCard: optional loyalty card.

• ClientAddress: optional home address.
• Indexed on ClientId and full name.

Address

Shared address entity used by both clients and employees:

• Standard address fields with optional State.

• Two relationships:

• EmployeeResidents

• ClientResidents

Loyalty Cards

ClientCard

Optional card associated with a client:

• Attributes like Points, Status, ExpiryDate, and CardImage.
• Linked to exactly one CardHolder (Client).

Products and Categories

Category

Used to classify Products. Each has:

• A Name and Description.
• Linked to multiple products.
• Indexed by Name.

Product

Represents a purchasable item in the system:

• Attributes for physical characteristics (Weight, Dimensions, EnergyConsumption, etc.).
• Metadata (Colours, Description, ReleaseDate, Images).
• Belongs to a Category.

Indexed on:

• Reference (primary key)
• Combination of Brand and Name.

Specialized Product: Television

Inherits from Product, and adds:

• ScreenSize, Technology, RefreshRate
• Smart features: SmartTv, OperatingSystem, RemoteControl
• Inputs: NumberOfHdmi, NumberOfUsb, HeadphoneJack
• Audio/video capabilities like Hdr, AudioTechnology

This inheritance allows reusing common product properties while enriching them with television-specific attributes.