Dynamic Database Schema

Using dynamic sql in a procedure to create schemas and tables

ok, fixed all the misspellings, incorrect syntax and non-unique names. all better.
added a bit of error handling. hope it gets you going again.

USE master
GO
if not exists (select 1 from master.sys.databases where name = 'CTUDB')
CREATE DATABASE CTUDB
GO
USE CTUDB
GO
if object_id('AddCampus_proc','P') is not null
    begin
        drop proc AddCampus_proc;
    end;
GO
CREATE PROCEDURE AddCampus_proc(@campus varchar(50)
)
AS
BEGIN
DECLARE @DynamicSQL varchar(MAX)
SET @DynamicSQL = 'CREATE schema '+quotename(@campus)+''
begin try
    EXEC (@DynamicSQL);
end try
begin catch
    if error_number() <> 2759 --this just means the schema already exists.
        begin
            print(error_number())
            print(error_message())
        end
end catch

SET @DynamicSQL = 'CREATE table '+quotename(@campus)+'.Student_tbl(
StudentID numeric(4,0) not null PRIMARY KEY,
Name varchar(50) not null,
Surname varchar(50) not null,
ID_numeric numeric(13,0) not null,
Address varchar(100) not null,
CONSTRAINT '+@campus+'_Student_tbl_CheckStudentID check (len(StudentID) = 4),
CONSTRAINT '+@campus+'_Student_tbl_CheckIDnumeric check (len(ID_numeric) = 13)
);'
--print (@DynamicSQL);
EXEC (@DynamicSQL);

SET @DynamicSQL = 'CREATE table '+quotename(@campus)+'.Course_tbl(
CourseID integer not null PRIMARY KEY,
CourseName varchar(50) not null,
Description varchar(100) not null,
StudentID numeric(4,0) not null,
FOREIGN KEY (StudentID) REFERENCES ' + quotename(@campus) + '.Student_tbl(StudentID),
CONSTRAINT '+@campus+'_Course_tbl_CheckStudentID check (len(StudentID) = 4)
);'
--print (@DynamicSQL);
EXEC (@DynamicSQL);

SET @DynamicSQL = 'CREATE table '+quotename(@campus)+'.ClassMarks_tbl(
ClassMarksID integer not null PRIMARY KEY,
StudentID numeric(4,0) not null,
CourseID integer not null,
Semester1_Mark1 integer not null check (Semester1_Mark1 between 0 and 100),
Semester1_Mark2 integer not null check (Semester1_Mark2 between 0 and 100),
Semester1_Mark3 integer not null check (Semester1_Mark3 between 0 and 100),
Semester1_Average integer not null check (Semester1_Average between 0 and 100),
Semester1_Test_Mark integer not null check (Semester1_Test_Mark between 0 and 100),
Semester2_Mark1 integer not null check (Semester2_Mark1 between 0 and 100),
Semester2_Mark2 integer not null check (Semester2_Mark2 between 0 and 100),
Semester2_Mark3 integer not null check (Semester2_Mark3 between 0 and 100),
Semester2_Average integer not null check (Semester2_Average between 0 and 100),
Semester2_Test_Mark integer not null check (Semester2_Test_Mark between 0 and 100),
FOREIGN KEY (StudentID) REFERENCES ' + quotename(@campus) + '.Student_tbl(StudentID),
FOREIGN KEY (CourseID) REFERENCES ' + quotename(@campus) + '.Course_tbl(CourseID),
CONSTRAINT '+@campus+'_ClassMarks_tbl_CheckStudentID check (len(StudentID) = 4)
);'
--print (@DynamicSQL);
EXEC (@DynamicSQL);

SET @DynamicSQL = 'CREATE table '+quotename(@campus)+'.Facilitator_tbl(
FacilitatorID integer not null PRIMARY KEY,
Name varchar(50) not null,
Surname varchar(50) not null,
Address varchar(100) not null,
Paycheck decimal(19,4) not null,
CourseID integer not null,
FOREIGN KEY (CourseID) REFERENCES ' + quotename(@campus) + '.Course_tbl(CourseID)
);'
--print (@DynamicSQL);
EXEC (@DynamicSQL);

SET @DynamicSQL = 'CREATE table '+quotename(@campus)+'.Parents_tbl(
ParentID integer not null PRIMARY KEY,
Name varchar(50) not null,
Surname varchar(50) not null,
ID_numeric numeric(13,0) not null,
StudentID numeric(4,0) not null,
FOREIGN KEY (StudentID) REFERENCES ' + quotename(@campus) + '.Student_tbl(StudentID),
CONSTRAINT '+@campus+'_Parents_tbl_StudentID check (len(StudentID) = 4)
);'
--print (@DynamicSQL);
EXEC (@DynamicSQL);
END
GO
EXEC AddCampus_proc 'Nelspruit'
EXEC AddCampus_proc 'Roodepoort'
EXEC AddCampus_proc 'Sandton'
EXEC AddCampus_proc 'Boksburg'
EXEC AddCampus_proc 'Pretoria'
EXEC AddCampus_proc 'Cape_Town'
EXEC AddCampus_proc 'Vereniging'
EXEC AddCampus_proc 'Bloemfontein'
EXEC AddCampus_proc 'Polokwane'
EXEC AddCampus_proc 'Durban'
EXEC AddCampus_proc 'Stellenbosch'
EXEC AddCampus_proc 'Port_Elizabeth'
EXEC AddCampus_proc 'Pochefstroom'
EXEC AddCampus_proc 'Auckland_Park'

Database Structure involving dynamic fields

You have reinvented an old antipattern called Entity-Attribute-Value. The idea of custom fields in a table is really logically incompatible with a relational database. A relation has a fixed number of fields.

But even though it isn't properly relational, we still need to do it sometimes.

There are a few methods to mimic custom fields in SQL, though most of them break rules of normalization. For some examples, see:

Product table, many kinds of product, each product has many parameters on StackOverflow
My presentation Extensible Data Modeling with MySQL
My book SQL Antipatterns: Avoiding the Pitfalls of Database Programming

Dynamic schema changes in Cassandra

I would personally go with the 2nd solution - having columns for each data type that is used, and use the attribute name as the last component of the primary key (see examples in my previous answers on that topic:

Cassandra dynamic column family
How to handle Dynamic columns in Cassandra
How to handle Dynamic columns in Cassandra
How to understand the 'Flexible schema' in Cassandra？

First solution has following problems:

If you do schema modification from the code, then you need to coordinate these changes, otherwise you will get schema disagreement that will must be resolved by admins by restarting the nodes. And coordinated change will either slowdown the data insertion, or will create a single point of failure
Existence of many columns has significant performance impact. For example, per this very good analysis by The Last Pickle, having 100 columns instead of 10 increases read latency more than 10 times
You can't change attribute type if you'll need - in the solution with attribute as clustering column, you can just start to put attribute as another type. If you have attribute as column, you can't do that, because Cassandra doesn't allow to change column type (don't try to drop column & add it back with the new type - you'll corrupt your existing data). So you will need to create a completely new column for that attribute.

How to handle dynamic schema and dynamic data in php mysql?

You may like my presentation Extensible Data Modeling with MySQL. I talk about requirements and several solutions for dynamic schemas.

Extra Columns
Entity-Attribute-Value
Class Table Inheritance
Serialized LOB & Inverted Indexes
Online Schema Changes
Non-relational databases

Each solution has its own strengths and weaknesses. Knowing about them all gives you more tools to use in a given application.

For storing form data, I'd suggest either the Serialized LOB & Inverted Indexes, or else use a non-relational database that can index fields within a document.

Change database schema during runtime based on logged in user and shared schema

Found two solutions, both solved my problem.

Solution 1 -
Created a hibernate interceptor and add dynamic schema in SQL query.

https://dzone.com/articles/hibernate-dynamic-table-routin

public class HibernateInterceptor extends EmptyInterceptor {

    @Override
        public String onPrepareStatement(String sql) {
              String prepedStatement = super.onPrepareStatement(sql);
              prepedStatement = prepedStatement.replaceAll("secure.identitymanagement", "my_dynamic_goodness");
              return prepedStatement;
        }
    
    }

Solution 2 -
Configured two different schema - one for shared/common schema and another one for the user-specific schema.

This blog explains both "Database per tenant" and "Schema per tenant".
https://callistaenterprise.se/blogg/teknik/2020/09/19/multi-tenancy-with-spring-boot-part1/