Relation with tables

Question

Currently, I'm working for my assignment project called 'Competition Management' and I don't know much about SQL and PHP. Afaik, my questions should be workout in PHP to do this or that but I'm just curious with these SQL. You can ignore the empty varchar.

2 Questions

1. Player Table is related to Competition and Association Table. Do I have to do something with it or just let it be?

2. In Competition Table, you can see config_competition field... but, the field should contain data inside it... in term of sport competition, lets say we have football sport, gaming e-sport, athlete sport, and etc... In this case, should I create a new table? If yes, how do I make it related with the Competition Table?

This is Organisation Table (who creates a competition)

CREATE TABLE organisation (
    id_organisation varchar() PRIMARY KEY,
    name_organisation varchar(),
    password varchar()
);

This is Competition Table (Created by Organiser)

CREATE TABLE competition (
    id_competition varchar() PRIMARY KEY,
    name_competition varchar(),
    config_competition varchar()
);

This is Association Table (participate a competition)

CREATE TABLE association (
    id_association varchar() PRIMARY KEY,
    name_association varchar(),
    password varchar()
);

This is Player Table (registered by Association to participate player in a competition)

CREATE TABLE player (
    id_player varchar() PRIMARY KEY,
    name_player varchar()
);

Answer 1

You need to use foreign keys to refer to another table's primary keys. In that way, you can build a relationship between tables. In other words, you can connect the tables that have relationships with each other.

I can suggest you use MySQL workbench and draw an ER diagram of your project in the first phase of your project. After that, you can create a database.

Also, before all these work, you may need to look at the relational data models, one-to-many, one-to-one, and many-to-many. Then you can select the appropriate relations between tables.

For your second question below is an answer:

CREATE TABLE category (
    category_id int PRIMARY KEY,
    category_name varchar(),
);

CREATE TABLE competition (
id_competition varchar() PRIMARY KEY,
name_competition varchar(),
category_id int FOREIGN KEY REFERENCES category(category_id)

);