livegirllove
asked on
Should I create a seperate table?
I have a MySQL db that I use to create and store invoices. I currently have one field for hardware and one for hardware text. So I manually total the price of the hardware and then enter it. My script then adds the tax and adds the labour cost to create the total.
I have decided that this isnt good enough. I want to be able to enter a qty, description, and cost per unit for each item.
So would it be better to create a new table for hardware containing fields id, invoiceid, qty, description, unitcost
or just add say qty, description, unitcost : qty2, description2, unitcost2 : qty3, description3, unitcost3 etc.... to the current invoice table. Obviously this method limits the amount of peices of hardware to the amount of fields ive created but it would be easy to do.
If I create a new table, how should i be thinking about entering each hardware item? I guess my form would have to have 3 fields to enter each hardware item, but can i submit that part of the form and then create another set of text fields for the next item? Or should I create enough text fileds for a certain amount of items (say 10)?
And how would I be able to submit a hardware item that has the invoiceid if the invoice hasnt been created yet? Should I create an invoice, then have another page to add hardware to it?
Any suggestions would be great,
I have decided that this isnt good enough. I want to be able to enter a qty, description, and cost per unit for each item.
So would it be better to create a new table for hardware containing fields id, invoiceid, qty, description, unitcost
or just add say qty, description, unitcost : qty2, description2, unitcost2 : qty3, description3, unitcost3 etc.... to the current invoice table. Obviously this method limits the amount of peices of hardware to the amount of fields ive created but it would be easy to do.
If I create a new table, how should i be thinking about entering each hardware item? I guess my form would have to have 3 fields to enter each hardware item, but can i submit that part of the form and then create another set of text fields for the next item? Or should I create enough text fileds for a certain amount of items (say 10)?
And how would I be able to submit a hardware item that has the invoiceid if the invoice hasnt been created yet? Should I create an invoice, then have another page to add hardware to it?
Any suggestions would be great,
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Could you give a quick description on the use of each table:
Client Data: obviously holds the client contact/billing details
Hardware: ? what is the purpose of this table
invoice: must be the invoice, however it has some overlap with Hardware.
Explain hardware and we will see what we come up with.....
Client Data: obviously holds the client contact/billing details
Hardware: ? what is the purpose of this table
invoice: must be the invoice, however it has some overlap with Hardware.
Explain hardware and we will see what we come up with.....
It will be a good practice the use the same fieldname for similar data:
invoice:
`invoiceid` int(9) NOT NULL auto_increment, <---- change here
`clientid` int(11) NOT NULL default '0',
Client Data:
`clientid` mediumint(9) NOT NULL auto_increment, <---- change here
Hardware:
`hwid` int(9) NOT NULL auto_increment, <---- optional, maybe not necesary
`invoiceid` int(9) NOT NULL default '0',
invoice:
`invoiceid` int(9) NOT NULL auto_increment, <---- change here
`clientid` int(11) NOT NULL default '0',
Client Data:
`clientid` mediumint(9) NOT NULL auto_increment, <---- change here
Hardware:
`hwid` int(9) NOT NULL auto_increment, <---- optional, maybe not necesary
`invoiceid` int(9) NOT NULL default '0',
To refine your database design you can build a "product" table, so you dont have to enter description and cost every time. Something like:
Hardware:
`invoiceid` int(9) NOT NULL default '0', <------ both will be the key, no 'id' key needed
`prodid` text NOT NULL,
`qty` int(4) NOT NULL default '0',
`unitcost` decimal(10,2) NOT NULL default '0.00', <---- This cost may differ from official cost in Products table
KEY `hwid` (`invoiceid`,`prodid`) <---- notice the compound key
) TYPE=MyISAM COMMENT='Hardware table' AUTO_INCREMENT=1 ;
Products:
'prodid' int(9) NOT NULL,
'description' text NOT NULL
'unitcost' decimal(10,2) NOT NULL
KEY 'prodid' ('prodid')
) TYPE=MyISAM COMMENT='Product table';
Hardware:
`invoiceid` int(9) NOT NULL default '0', <------ both will be the key, no 'id' key needed
`prodid` text NOT NULL,
`qty` int(4) NOT NULL default '0',
`unitcost` decimal(10,2) NOT NULL default '0.00', <---- This cost may differ from official cost in Products table
KEY `hwid` (`invoiceid`,`prodid`) <---- notice the compound key
) TYPE=MyISAM COMMENT='Hardware table' AUTO_INCREMENT=1 ;
Products:
'prodid' int(9) NOT NULL,
'description' text NOT NULL
'unitcost' decimal(10,2) NOT NULL
KEY 'prodid' ('prodid')
) TYPE=MyISAM COMMENT='Product table';
ASKER
Yes the overlap in hardware and invoice is that I still need to be able to enter hardware while Im working on getting the new table and script to work. In the end I will get rid of invoice.hardware and invoice.hardwaretext.
Hardware table will hold:
id
invoiceid: the id of the invoice the hardware is attached to
qty: number of items
unitcost: cost per item
description: description of the product
eg:
5 | Acer 5240 Motherboard | 112.95
which is 5 motherboards at 112.95 each
Hardware table will hold:
id
invoiceid: the id of the invoice the hardware is attached to
qty: number of items
unitcost: cost per item
description: description of the product
eg:
5 | Acer 5240 Motherboard | 112.95
which is 5 motherboards at 112.95 each
ASKER
invoice
`id` int(9) NOT NULL auto_increment,
`clientid` int(11) NOT NULL default '0',
`rate` decimal(10,2) NOT NULL default '0.00',
`hours` decimal(10,2) NOT NULL default '0.00',
`hardware` decimal(10,2) NOT NULL default '0.00',
`hardwaretext` text NOT NULL,
`comment` text NOT NULL,
`total` decimal(10,2) NOT NULL default '0.00',
`date` datetime NOT NULL default '0000-00-00 00:00:00',
`tax` decimal(10,2) default NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=2992 ;
Client Data:
`id` mediumint(9) NOT NULL auto_increment,
`firstname` varchar(100) default NULL,
`email` varchar(100) default NULL,
`aim` varchar(100) default NULL,
`msn` varchar(100) default NULL,
`yahoo` varchar(100) default NULL,
`irc` varchar(100) default NULL,
`ircalias` varchar(100) default NULL,
`referedby` varchar(100) default NULL,
`comment` longtext,
`address` text NOT NULL,
`address2` text NOT NULL,
`city` text NOT NULL,
`state` text NOT NULL,
`zip` text NOT NULL,
`phone` text NOT NULL,
`cell` text NOT NULL,
`fax` text NOT NULL,
`lastname` varchar(100) NOT NULL default '',
`title` text NOT NULL,
`company` varchar(40) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=21 ;
Hardware:
`id` int(9) NOT NULL auto_increment,
`invoiceid` int(9) NOT NULL default '0',
`qty` int(4) NOT NULL default '0',
`unitcost` decimal(10,2) NOT NULL default '0.00',
`description` text NOT NULL,
KEY `id` (`id`)
) TYPE=MyISAM COMMENT='Hardware table' AUTO_INCREMENT=1 ;