How can I link two tables that has multiple values that are related

2018-07-01 php mysql
//Php code to generate entry IDs to link the two tables
    function generateRandomString($length = 10) {
        $characters = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
        $charactersLength = strlen($characters);
        $randomString = '';
        for ($i = 0; $i < $length; $i++) {
            $randomString .= $characters[rand(0, $charactersLength - 1)];
        }
        return $randomString;
    }


    $entry_id=generateRandomString();

the above generated id will be inserted into the multiple or single table and the music entry name table to link the entries with their respective name of performers in the group or if individual.

for the scenario is i have an entry system which allows schools to register and submit entries to the database.

So the school will have multiple entries say for example an entry for a group name: Dancing expression which has three members and has selected multiple categories; Individual name: John doe which has a single category;

so just using the ID the schools get when register wont be effective in identifying the the entry the school made in the different tables. I have three tables:

registration table- contain: email, password, address, name;

music_entry name table- contains: name, age, registration_id, entry_id ;

single_multiple_categories table- contains: music_title, multiple_or_single_categories, registration_id, entry_id

Categories for music are:

Popular music

Opera genres

Folk music

Instrumental music

click here for image of database design in mysql

I want to know if what i did is practical and is the best way of solving such a scenario in terms of linking the tables with the generated entry_id and not just with just the registration_id foreign key which only links the music entry and multiple or single category table to the registration table.

Answers

Related