SitePoint Sponsor

User Tag List

Results 1 to 13 of 13

Thread: subtables

  1. #1
    SitePoint Addict
    Join Date
    Sep 2001
    Location
    UK
    Posts
    303
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    subtables

    Hi,

    I am trying to create subtables 'router' and 'switch' under my supertable 'Products'. Could anyone tell me how to go about doing this either in phpMyAdmin or using sql. Thanks.

    Azaar

    Update: I'm using MySQL

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    create table products
    ( id integer not null primary key auto_increment
    , name varchar(99) not null
    , type varchar(9) not null
    );
    insert into products ( name, type ) values ( 'foo', 'router' );
    insert into products ( name, type ) values ( 'bar', 'router' );
    insert into products ( name, type ) values ( 'qux', 'switch' );
    
    create table routers
    ( productid integer not null primary key
    , foreign key ( productid ) references products ( id )
    , sockets tinyint 
    , evaluation varchar(24)
    );
    insert into routers values ( 1, 11, 'fabulous' );
    insert into routers values ( 2, 15, 'fantastic' );
    
    create table switches
    ( productid integer not null primary key
    , foreign key ( productid ) references products ( id )
    , wires tinyint 
    , speed integer
    );
    insert into switches values ( 3, 5, 1000 );
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Sep 2001
    Location
    UK
    Posts
    303
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you very much r937.

    Am i right in thinking that if I INSERT a product into table 'routers' the relevant fields will be added to the 'products' table ?

    I was lead to believe I need to use UNDER when creating the subtables.

    Thanks

    Azaar

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Azaar
    Am i right in thinking that if I INSERT a product into table 'routers' the relevant fields will be added to the 'products' table ?
    no, you have to add it to products table first, and then add it to routers

    I was lead to believe I need to use UNDER when creating the subtables.
    i've never heard of this -- do you have a reference?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Sep 2001
    Location
    UK
    Posts
    303
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no, you have to add it to products table first, and then add it to routers
    Is there anyway of making the addition to the products table automatic?

    i've never heard of this -- do you have a reference?
    Couple of places, including here

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Azaar
    Is there anyway of making the addition to the products table automatic?
    only in a stored procedure

    remember, the parent table row must be inserted first, before the child table row can refer to it

    as for the UNDER thingie, that won't work in mysql
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Sep 2001
    Location
    UK
    Posts
    303
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    only in a stored procedure
    I'm afraid I don't know what a stored procedure is.

  8. #8
    SitePoint Addict
    Join Date
    Sep 2001
    Location
    UK
    Posts
    303
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, I've answered my own question... http://dev.mysql.com/doc/mysql/en/st...rocedures.html

    Thanks very much for your help and explanation.

    Azaar

  9. #9
    SitePoint Addict
    Join Date
    Sep 2001
    Location
    UK
    Posts
    303
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    rudy, would you recommend I use a different type of database if I want to do this? PostgreSQL ?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i'm sorry, i don't understand, i thought you said you were using mysql

    do you mean, you would actually switch databases so that you could have stored procedures so that you could generate a parent row insert ahead of a child row insert?

    why not just insert the parent row first and then the child row?

    maybe i don't undertstand
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Addict
    Join Date
    Sep 2001
    Location
    UK
    Posts
    303
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I was intending to use MySQL but as development is yet to begin I am able to consider other options. I was wondering if you could recommend the best course of action.

    Thanks

    Azaar

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Azaar
    I was wondering if you could recommend the best course of action.
    sure, i'd be happy to

    use mysql

    insert the products row first, then insert the row in whichever product type subtable second

    these will be two consecutive INSERT statements issued by your application code

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Addict
    Join Date
    Sep 2001
    Location
    UK
    Posts
    303
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    *chuckle*

    Thanks for your assistance.

    Azaar


Bookmarks

Posting Permissions

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