Have you made sure that the parent table(s) is type INNODB as well? Also, in my experience, it is best to let the database be in charge of as many database functions as possible as it helps maintain the data's integrity. In other words, use foreign keys, indexing for speeding up searching, 'on delete cascade' to keep the database clean, etc.
--Nicole --------------------------- Nicole Swan Web Programming Specialist Carroll College CCIT (406)447-4310 -----Original Message----- From: Vincent Jordan [mailto:[EMAIL PROTECTED] Sent: Monday, July 19, 2004 5:19 PM To: 'Matthew McNicol' Cc: [EMAIL PROTECTED] Subject: Re: [PHP-DB] DB table creation question I think I may have gone over my head. I am fairly new to mysql and php. My host only allows access to mysql via phpmysql. I am used to using mysqlcc to connect to the db's and make changes. By default all tables created are MYISAM, when I try to input a INNODB table with foreign keys I either get syntax error or I believe it is errno(150). Anyhow, is there a piece of software or a webpage out there where I can input how I want the database to be made and it will spit out a .sql file that can be uploaded to phpmyadmin or would someone be kind enough to "hold my hand" as I attempt to create this with proper syntax myself. Another note, instead of using indexes and keys can I have tables relate to each other by php script or am I asking for trouble as it grows. This database will be small at first however I expect it to grow to over 5k of customer records after the third month and in stages of about 200 records monthly after that. Thanks for all the help everyone has provided thusfar. -----Original Message----- From: Matthew McNicol [mailto:[EMAIL PROTECTED] Sent: Monday, July 19, 2004 3:08 AM To: Vincent Jordan Cc: [EMAIL PROTECTED] Subject: [SPAM] Re: [PHP-DB] RE: [SPAM] Re: [PHP-DB] DB table creation question at the moment if a value is say inserted into the 'warranty_info' table you are just checking that the 'product_id' or 'cust_id' already exists in 'product_info' and 'customer_info'. you still have to populate them first, otherwise the referential integrity will return an error when you insert into 'warranty_info'. similarly if you delete a record from 'warranty_info' you might actually delete the associated customer record. is this what you want? at the moment it looks like the 'rma_info' table is not linked to any other table? personally, I would not go so far as to specify 'FOREIGN KEY ... REFERENCES' or 'ON UPDATE CASCADE ON DELETE RESTRICT' in the create table code. I would use the application code to make sure a customer exists before referencing them in the 'product_info' or 'warranty_info' tables. similarly I would use the application code to delete table record out of each appropriate table. I would use the primary and secondary index assuming you are working with more than 100 records in each table. Vincent Jordan wrote: > Does this seem to be correct? With this if value was inserted into a > table will the tables with the FK's automatically be updated? Here is > the DB design as I have it now. Let me know if all is correct please. > > CREATE TABLE customer_info ( > cust_id int (6) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, > first_name varchar (50) NOT NULL, > last_name varchar (50) NOT NULL, > address varchar (50) NOT NULL, > address2 varchar (50) NULL, > city varchar (50) NOT NULL, > state varchar (50) NOT NULL, > zip varchar (50) NOT NULL, > home_phone varchar (15) NOT NULL, > work_phone varchar (15) NULL, > other_phone varchar (15) NULL, > email varchar (50) NOT NULL, > ); > CREATE TABLE product_info ( > product_id int (6) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, > cust_id int (6) NOT NULL, > model varchar (50) NOT NULL, > serial varchar (20) NULL, > FOREIGN KEY (customer_info_cust_id), > REFERENCES customer_info(cust_id), > ON UPDATE CASCADE ON DELETE RESTRICT, > INDEX (cust_id), > FOREIGN KEY (cust_id), > REFERENCES customer_info(cust_id), > ); > CREATE TABLE warranty_info ( > warranty_id int (6) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, > product_id int (6) NOT NULL, > cust_id (6) NOT NULL, > mfg_warranty tinyint (1) NOT NULL default '1', > gold_warranty tinyint (1) NOT NULL default '0', > gold_warranty_id int (16) NULL, > FOREIGN KEY (customer_info_cust_id), > REFERENCES customer_info(cust_id), > ON UPDATE CASCADE ON DELETE RESTRICT, > INDEX (cust_id), > FOREIGN KEY (cust_id), > REFERENCES customer_info(cust_id), > FOREIGN KEY (product_info_product_id), > REFRENCES product_info(product_id), > ON UPDATE CASCADE ON DELETE RESTRICT, > INDEX (product_id), > FOREIGN KEY (prod_id), > REFRENCES product_info (product_id), > ); > CREATE TABLE rma_info ( > rma_id int (6) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, > assigned_to varchar (50) NULL, > logged_by varchar (50) NULL, > date datetime NOT NULL, > time_spent datetime (6) NULL, > status varchar (50) NOT NULL, > priority varchar (50) NULL, > closed_by varchar (50) NULL, > completed_by varchar (50) NULL, > date_closed datetime (6) NULL, > billable tinyint(1)NULL, > billed tinyint (1) NULL, > cost varchar (6) NULL, > last_modified_by varchar (50) NULL, > last_modified_date datetime NULL, > ); > > > > -----Original Message----- > From: Matthew McNicol [mailto:[EMAIL PROTECTED] > Sent: Sunday, July 18, 2004 5:46 PM > To: Vincent Jordan > Cc: [EMAIL PROTECTED] > Subject: [SPAM] Re: [PHP-DB] DB table creation question > > > use the mysql 'auto increment' on a 'int' 'customer_id' field to > generate a unique customer id. > > the RMA number is associated with a 'customer_id' since the > 'customer_id' field is in both the customers table and the RMA table. > Note that currently you have 'custid' and 'Customerid' respectively > which you should change so that they are uniform (it helps with database > > maintenance is foreign keys are spelt the same as the primary key > equivalent). > > to assign a product to a customer, first create a products table which > defines each product. each product would have a unique 'product_id'. > second, create a table which will link the products (zero, one or more) > a customer has selected to the 'rma_id'. such tables would have the > following structure: > > table: product > product_id > product_desc > ... > > table: rma_product > rma_id > product_id > > > table: customer > customer_id > firstname > lastname > ... > > table: rma > rma_id > assignedto > loggedby > customer_id > ... > > > Do you follow? Do you understand in what order you should populate the > tables and how you can now create SQL queries to get the information you > > need? > > > > Vincent Jordan wrote: > >>I am creating a database to keep track of our warranty returns. >>Currently we do not have anything to track warranty info besides a big >>excel file. I would like to keep customer data, product info and RMA >>data in separate tables but still keep them related. >> >>If I had table customers: >>custid >>Firstname >>Lastname >>Address >>Address2 >>City >>State >>Zip >>Phone >>Email >> >>And table RMA >>Rmaid >>Assignedto >>Loggedby >>Customerid >>Date >>Timespent >>Status >>Priority >>Closedby >>Completedby >>Dateclosed >>Billable >>Billed >>Cost >>Lastmodifiedby >>Lastmodifieddate >> >>What I want to ensure is that if john smith fills out an RMA request >>online a customer id is assigned to John, the RMA number is associated >>with Johns customerid and I need to figure out how to assign a product >>to John. Also if john has more than one product assign products so > > when > >>I pull up Johns information I can see all of Johns products owned. >> >>I am running mysql latest version.. Any help would be greatly >>appriciated >> >> > > -- _________________ Matthew McNicol yellowmarker.co.uk PHP / MySQL web development -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
