subtables

[Azaar]

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

[r937]

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 );

[Azaar]

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

[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 ?

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?

[Azaar]

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

[r937]

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

[Azaar]

only in a stored procedure

I'm afraid I don't know what a stored procedure is.

[Azaar]

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

[Azaar]

rudy, would you recommend I use a different type of database if I want to do this? PostgreSQL ?

[r937]

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

[Azaar]

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

[r937]

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

[Azaar]

*chuckle*

Thanks for your assistance.

Azaar