0

I'm working on SQL Final project and I decided to create a database for a gym franchise.

How can I make sure that a client can join classes only to which they have a card for (correct access level and city). Also, if they have a "Premium" card they should be able to join lower tier classes together with Premium ones.

I will be inserting data as looking like that:

INSERT INTO clients (first_name, last_name, date_of_birth, PESEL, card, trainer, class)
VALUES
("John", "Smith", "24-08-1980", "34567238769", 3, NULL, 2);

where 3 is "id" of a Premium card in Warsaw and 2 is a class conducted in Frankfurt with access level 1. Here the city is not corresponding so SQL should not allow it. The same goes for access level of classes

I assume I need to create a CHECK constraint somewhere in clients table but not sure where to start.

This is my schema:

CREATE TABLE "clients" (
    "id" INTEGER,
    "first_name" TEXT NOT NULL,
    "last_name" TEXT NOT NULL,
    "date_of_birth" TEXT NOT NULL,
    "PESEL" TEXT UNIQUE NOT NULL,
    "card" INTEGER NOT NULL,
    "trainer" INTEGER,
    "class" INTEGER,
    PRIMARY KEY ("id"),
    FOREIGN KEY ("card") REFERENCES "cards" ("id"),
    FOREIGN KEY ("trainer") REFERENCES "trainers" ("id"),
    FOREIGN KEY ("class") REFERENCES "classes" ("id")
);

CREATE TABLE "classes" (
    "id" INTEGER,
    "name" TEXT NOT NULL UNIQUE,
    "intensity" TEXT NOT NULL CHECK("intensity" IN ('low', 'moderate', 'high')),
    "length" INTEGER NOT NULL,
    "trainer" INTEGER NOT NULL,
    "day_of_conduct" TEXT NOT NULL,
    "card" INTEGER NOT NULL,
    PRIMARY KEY ("id"),
    FOREIGN KEY ("card") REFERENCES "cards" ("id"),
    FOREIGN KEY ("trainer") REFERENCES "trainers" ("id")
);
CREATE TABLE "cards" (
    "id" INTEGER,
    "name" TEXT NOT NULL CHECK ("name" IN ('Standard', 'Silver', 'Premium')),
    "access" TEXT NOT NULL CHECK("access" IN ('1', '2', '3')),
    "gym" INTEGER NOT NULL,
    PRIMARY KEY ("id"),
    FOREIGN KEY ("gym") REFERENCES "gyms" ("id")
);
CREATE TABLE "gyms" (
    "id" INTEGER,
    "name" TEXT NOT NULL UNIQUE,
    "city" TEXT NOT NULL,
    "voivodeship" TEXT NOT NULL,
    PRIMARY KEY ("id")
);

skipped trainers for simplicity sake

Any help is appreciated

2
  • You might want to read up on triggers. Or perhaps it's a front-end issue. Why let a user sign up for a class they cannot take? Commented Nov 27, 2023 at 23:30
  • I was thinking about triggers too. Regarding letting the user sign up for classes, well, I thought that if I block it in database it would save the hustle of front-end. For the project I'm not doing any front end though
    – phnlg
    Commented Nov 28, 2023 at 9:18

1 Answer 1

0

Yes, you will need to check that the info to be inserted qualifies before inserting. Do that with a select on the appropriate tables combined with some logic in your code before attempting the insert.

BTW, the insert as written would fail. It has a null value for the trainer field. The trainer field has a foreign key, so null wouldn't be valid. Instead, you could have an entry in the trainer table for "staff" that you could use as a generic value though. That would satisfy the foreign key requirement.

To me, the design looks like it needs to be modified. With this "clients" table design, it will have to either allow for multiple entries for the same user or limit them to one card and one class at a time.

I suggest splitting it into at least two tables. First, have a "client" table that contains their personal information, but not any class info. If they can only have one card, then include it in the client table. Otherwise, create a separate table with userid-card pairs. The point is to have only one row with all the client's personal info like name, address, etc.

I assume that anyone can attend multiple classes. In that case, have a userclass table with a row for each user/class pairing, and foreign keys on customer and class tables, along with whatever other info it needs (trainer, card, etc.)

These are some ideas off the top of my head. Not sure if it answers your question, but it'll get you thinking. ;-)

1
  • Yes, I thought about creating additional tables too like client-trainer relationship or client - class But I’ll look into adding CHECK constraint to that too, looks like I useful thing to know
    – phnlg
    Commented Nov 28, 2023 at 6:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .