Site icon Adron's Composite Code

Building a Multi-Tenant Music Collector’s Database

Adron

UPDATE: A subsequent post is available in refactoring this work with Chat GTP-4.

Ok, I’ve gotten deep into collecting music again, after just doing the *streaming* thing for a decade plus. In this deep dive back into music and playing, composing, collecting, reactions, reviews, live shows, and related worlds coming together I’ve found it might be interesting to put together a multi-tenant database to start collecting all my effort’s collateral data together. This, is the beginning of that journey.

I’ve got some tunes cranked as I’m all set to dive deep into this, let’s get it done!

Music has always been a significant part of my life. From the melodies that accompany my daily routines to the anthems of my most memorable moments, it’s been a constant. As my collection grew, I realized I needed a better way to organize it. That’s when I stumbled upon the concept of multi-tenancy databases and decided to give it a shot with PostgreSQL. Here’s my experience.

Why Did I Choose This Database?

My music collection started with a few albums but quickly expanded to encompass various genres, artists, and eras. I needed a structured way to keep track of everything. The multi-tenant music collector’s database seemed like the perfect solution because:

What Did I Learn About Multi-Tenant Systems?

Before diving in, I had to understand what multi-tenancy meant. It’s a system where a single instance of software can serve multiple users, but each user’s data remains isolated and private. The shared database model, where all users share the same database but have their data separated by a unique identifier, seemed like a straightforward approach.

Setting Up the Database:

Step 1: Install Your Database (or get one running)

Step 2: Create a New Database

Open the database (I’ll be using PostgreSQL this time) and create a new database for our music collector’s platform:

CREATE music_collectors;

Now I switch over to be sure I’m using that database specifically.

USE music_collectors;

Step 3: Create the Horse

Ya know, like this meme.

We’re at the “add small details” part.

So with that, here is the first draft of “small details”. I’ll elaborate on them after the following SQL.

-- Tenant table
CREATE TABLE tenants (
    tenant_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    notes TEXT,
    message TEXT
);

-- User table
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    tenant_id INT REFERENCES tenants(tenant_id),
    username VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL, -- Hashed password
    created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    notes TEXT,
    message TEXT
);

-- Roles table
CREATE TABLE roles (
    role_id SERIAL PRIMARY KEY,
    tenant_id INT REFERENCES tenants(tenant_id),
    name VARCHAR(255) NOT NULL,
    can_add BOOLEAN DEFAULT FALSE,
    can_edit BOOLEAN DEFAULT FALSE,
    can_delete BOOLEAN DEFAULT FALSE,
    created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    notes TEXT,
    message TEXT
);

-- User roles mapping table
CREATE TABLE user_roles (
    user_id INT REFERENCES users(user_id),
    role_id INT REFERENCES roles(role_id),
    PRIMARY KEY (user_id, role_id)
);

-- Musician table
CREATE TABLE musicians (
    musician_id SERIAL PRIMARY KEY,
    tenant_id INT REFERENCES tenants(tenant_id),
    name VARCHAR(255) NOT NULL,
    date_of_birth DATE,
    biography TEXT,
    created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    notes TEXT,
    message TEXT
);

-- Band table
CREATE TABLE bands (
    band_id SERIAL PRIMARY KEY,
    tenant_id INT REFERENCES tenants(tenant_id),
    name VARCHAR(255) NOT NULL,
    formation_date DATE,
    genre VARCHAR(255),
    country_of_origin VARCHAR(255),
    city_of_origin VARCHAR(255),
    active_status BOOLEAN DEFAULT TRUE,
    biography TEXT,
    website VARCHAR(255),
    social_media_links TEXT,
    label VARCHAR(255),
    discography TEXT,
    manager_name VARCHAR(255),
    contact_email VARCHAR(255),
    created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    notes TEXT,
    message TEXT
);

-- Band members table
CREATE TABLE band_members (
    band_member_id SERIAL PRIMARY KEY,
    band_id INT REFERENCES bands(band_id),
    musician_id INT REFERENCES musicians(musician_id),
    start_date DATE,
    end_date DATE,
    contribution TEXT,
    created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    notes TEXT,
    message TEXT
);

-- Albums table
CREATE TABLE albums (
    album_id SERIAL PRIMARY KEY,
    band_id INT REFERENCES bands(band_id),
    title VARCHAR(255) NOT NULL,
    release_date DATE,
    created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    notes TEXT,
    message TEXT
);

-- Songs table
CREATE TABLE songs (
    song_id SERIAL PRIMARY KEY,
    album_id INT REFERENCES albums(album_id),
    title VARCHAR(255) NOT NULL,
    songwriter VARCHAR(255),
    duration TIME,
    created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    notes TEXT,
    message TEXT
);

That creates a database with an entity diagram that would look like this.

The Collector’s Database, created with dbdiagram.io.

This schema provides a foundation for a multi-tenancy system using the shared database model. Each table has a tenant_id column to segregate data by tenant. The users and roles tables allow for user management and role-based permissions. The music-related tables (musicians, bands, band_members, albums, and songs) capture the details you specified. The default columns for audit purposes (created, updated, notes, and message) are also included in each table.

Let’s dive into the details of each table and their respective fields in the proposed multi-tenancy music collector’s database:

1. tenants Table:

2. users Table:

3. roles Table:

4. user_roles Table:

5. musicians Table:

6. bands Table:

7. band_members Table:

8. albums Table:

9. songs Table:

I’ve crafted a shared database model that’s both efficient and ensures data isolation (I hope, notice any mistakes here?). Let’s break it down.

The Tenancy Magic:

When I set out to design this, my primary goal was to ensure that each tenant’s data remains isolated, even though they all share the same database. The magic lies in a simple yet powerful concept: the tenant_id.

The tenants Table:

This is where it all begins. The tenants table is the cornerstone of our multi-tenancy system. Each tenant gets a unique tenant_id which acts as their unique identifier in our shared database.

The users and roles Tables:

Now, here’s where things get interesting. Each tenant can have multiple users, and each user can have specific roles. But how do we ensure that a user from Tenant A doesn’t accidentally get access to Tenant B’s data? You guessed it: the tenant_id.

Both the users and roles tables have a tenant_id column. This ensures that users and roles are always associated with a specific tenant. When a user logs in, we know exactly which tenant they belong to, and we can tailor their experience accordingly.

The Data Isolation:

Every time we add a new table to our database, whether it’s for storing details about bands, musicians, albums, or songs, we always include the tenant_id column. This acts as a filter. When Tenant A wants to fetch their list of bands, we use their tenant_id to filter out the bands that belong to them and only them. Tenant B, with a different tenant_id, will get their own separate list. It’s like each tenant has their own private room in a large music mansion.

The Heartbeat: musicians Table:

Music starts with the artist, and that’s precisely where our journey begins. The musicians table is where we store the soul of our collection.

The Ensemble: bands Table:

Ah, the bands! Where musicians come together to create magic. The bands table is where we capture the essence of these musical groups.

The Crew: band_members Table:

Behind every great band are its members. The band_members table is where we celebrate these individual stars and their contributions.

The Masterpieces: albums and songs Tables:

Music is all about the tunes, and that’s what the albums and songs tables are all about.

Wrapping Up:

Building this musical universe was like composing a symphony. Every table, every field, is a note that adds depth to our music collection. Whether you’re a seasoned collector or just starting out, this database is your ticket to a world of musical wonders. So, put on your headphones, dive into the data, and let the music take you away!

Until the next jam session, keep thrashing! 🤘🏻

Exit mobile version