SitePoint Sponsor |
|
User Tag List
Results 1 to 13 of 13
Thread: subtables
Hybrid View
-
Jan 26, 2005, 12:15 #1
- 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
-
Jan 26, 2005, 12:48 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 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 );
-
Jan 26, 2005, 12:56 #3
- 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
-
Jan 26, 2005, 13:05 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Originally Posted by Azaar
I was lead to believe I need to use UNDER when creating the subtables.
-
Jan 26, 2005, 13:10 #5
- 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
i've never heard of this -- do you have a reference?
-
Jan 26, 2005, 13:21 #6
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Originally Posted by Azaar
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
-
Jan 26, 2005, 13:55 #7
- Join Date
- Sep 2001
- Location
- UK
- Posts
- 303
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
only in a stored procedure
-
Jan 26, 2005, 13:58 #8
- 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
-
Jan 26, 2005, 14:05 #9
- 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 ?
-
Jan 26, 2005, 14:59 #10
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 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
-
Jan 26, 2005, 17:14 #11
- 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
-
Jan 26, 2005, 18:02 #12
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Originally Posted by Azaar
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
-
Jan 26, 2005, 18:22 #13
- Join Date
- Sep 2001
- Location
- UK
- Posts
- 303
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
*chuckle*
Thanks for your assistance.
Azaar
Bookmarks