Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2014
    Posts
    1

    Unanswered: Join on 4 or more tables (SQL)

    As an exercise I am working on building my first ever database. I tried to create statements for all the tables in my database. I was wondering if any of the foreign keys could be removed?

    The second question I have is how to make a query for this database that uses a join on 4 or more tables.

    CREATE TABLE CLUB(
    cl_id INT PRIMARY KEY NOT NULL,
    naam TEXT NOT NULL,
    adres VARCHAR(200) NOT NULL,
    dtm_opricht TEXT NOT NULL
    );


    CREATE TABLE STADION(
    sta_id INT PRIMARY KEY NOT NULL,
    cl_id INT REFERENCES CLUB(cl_id),
    naam TEXT NOT NULL,
    adres VARCHAR(200) NOT NULL,
    capaciteit INT NOT NULL,
    dtm_bouw TEXT NOT NULL
    );

    CREATE TABLE TECHNISCHDIRECTEUR(
    td_id INT PRIMARY KEY NOT NULL,
    cl_id INT REFERENCES CLUB(cl_id),
    naam TEXT NOT NULL,
    adres VARCHAR(200) NOT NULL,
    salaris REAL NOT NULL,
    nationaliteit TEXT NOT NULL,
    geslacht CHAR NOT NULL,
    dtm_geboorte TEXT NOT NULL
    );

    CREATE TABLE FINANCIEELDIRECTEUR(
    fd_id INT PRIMARY KEY NOT NULL,
    cl_id INT REFERENCES CLUB(cl_id),
    naam TEXT NOT NULL,
    adres VARCHAR(200) NOT NULL,
    salaris REAL NOT NULL,
    nationaliteit TEXT NOT NULL,
    geslacht CHAR NOT NULL,
    dtm_geboorte TEXT NOT NULL
    );


    CREATE TABLE HOOFDTRAINER(
    ht_id INT PRIMARY KEY NOT NULL,
    cl_id INT REFERENCES CLUB(cl_id),
    td_id INT REFERENCES TECHNISCHDIRECTEUR(td_id),
    naam TEXT NOT NULL,
    adres VARCHAR(200) NOT NULL,
    salaris REAL NOT NULL,
    nationaliteit TEXT NOT NULL,
    geslacht CHAR NOT NULL,
    dtm_geboorte TEXT NOT NULL
    );


    CREATE TABLE ASSISTENTTRAINER(
    at_id INT PRIMARY KEY NOT NULL,
    cl_id INT REFERENCES CLUB(cl_id),
    ht_id INT REFERENCES HOOFDTRAINER(ht_id),
    naam TEXT NOT NULL,
    adres VARCHAR(200) NOT NULL,
    salaris REAL NOT NULL,
    nationaliteit TEXT NOT NULL,
    geslacht CHAR NOT NULL,
    dtm_geboorte TEXT NOT NULL
    );

    CREATE TABLE SPELER(
    sp_id INT PRIMARY KEY NOT NULL,
    cl_id INT REFERENCES CLUB(cl_id),
    ht_id INT REFERENCES HOOFDTRAINER(ht_id),
    naam TEXT NOT NULL,
    adres VARCHAR(200) NOT NULL,
    salaris REAL NOT NULL,
    nationaliteit TEXT NOT NULL,
    geslacht CHAR NOT NULL,
    dtm_geboorte TEXT NOT NULL,
    positie TEXT NOT NULL,
    rugnummer INT NOT NULL
    );


    Any answer will be appriciated!
    Last edited by Rowan2x; 04-18-14 at 19:33.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Any foreign keys that aren't appropriate can (and should) be removed. You are the only person who can determine which foreign keys are appropriate.

    Start by a simple SELECT operation from one table. Add additional tables as appropriate for your needs. Don't worry about the difference between four and twenty-four, just get the information that you need.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Sep 2007
    Location
    Bangalore India
    Posts
    28
    If the tables going to have too much records, I suggest you not to join too much table...

  4. #4
    Join Date
    Nov 2014
    Posts
    3
    As other users I'm not sure if four or more table join is a good idea. Here you have nice examples with three table join: http://thomaslarock.com/2012/04/real...join-examples/
    According to your question about foreign keys. Foreign key in one table links to a primary key in another table. So.. a number of foreign keys is quite obvious, because you decide which tables are related.

    ...and according to your model,
    I've imported your sql ddl into Vertabelo. This is a nice online tool that enables to visual design your database.

    Here is your model:

    Click image for larger version. 

Name:	dbforum1.png 
Views:	0 
Size:	79.1 KB 
ID:	16063
    https://my.vertabelo.com/public-mode...=4562&zoom=1.0

    I believe now it's better to discuss your diagram.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •