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