Solved

Should I create a seperate table?

Posted on 2004-08-29
7
193 Views
Last Modified: 2006-11-17
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,
0
Comment
Question by:livegirllove
  • 3
  • 2
  • 2
7 Comments
 
LVL 55

Assisted Solution

by:Jaime Olivares
Jaime Olivares earned 250 total points
ID: 11928733
>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.

Second one is never a good solution. Standard solution is header/detail table pair, where header record is related to its details by a key, in this case, by invoice ID.

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

You can crete a formulary to accept 10 items and two option buttons: "Save and finish" and "Save and enter more"

> 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?  
 
Yes, you can store the invoice general info at the header table, and then ask for items and store them in detail table. But notice that if you cancel the invoice you have to delete both detail and header records (quering by ID, of course).

Good luck,
Jaime.
0
 
LVL 1

Accepted Solution

by:
waan earned 250 total points
ID: 11928760
Well,
you ask some very good questions.

The textbook solution would involve 2 tables, Invoice and InoiceItem.

Invoice would include fields such as:
InvoiceID
CustomerID
Address
Date
etc...

InvoiceItem would include:
InvoiceID
PartID
UnitCost
Qty

So, under this method, to add an entry to your invoice (a new item) you would first have to save the invoice to generate an InvoiceID.

One method of setting this up might be to create a page that is laid out like the invoice itself. There would be no provision for the entery of new hardware ites on the invoice.

You include a button to take you to another page to enter/maintain the actual detail of hardware items on the invoice. When completed, you could return to your invoice with all appropriate totals in place.

The idea of using multiple fields to store each hardware entry onthe invoice, in the same table is somewhat restricting.

This is certainly hwo I would approach the problem from the information you have provided.

There is a fair amount of code to go with this, but when it is done, you can use the same approach over and over....

I have written a series of pages recently that operate in a method similar to what I have described.

The page opens and if no recommendation_ID is present, it assumes we are creating a new one.
When the user presses the button to add individual lines to the recommendation, a function is called that saves the form, returns the recommendation_ID and then proceeds to the screen to enter the actual entries of the recommendation. This way the recommendation_ID is present for each item added to the list of recommendations. I also have a function that displays the list of recommendation items for a given recommendation. This is useful as the list list is displayed in more than one place.

Just a few quick thoughts, good luck....
0
 
LVL 1

Author Comment

by:livegirllove
ID: 11928795
Thanks for the responses.  Heres the info on my DB as I currently have it setup.  I'm going to start googling on your suggestions

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 ;
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Expert Comment

by:waan
ID: 11928820
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.....
0
 
LVL 55

Expert Comment

by:Jaime Olivares
ID: 11928823
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',
0
 
LVL 55

Expert Comment

by:Jaime Olivares
ID: 11928849
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';
0
 
LVL 1

Author Comment

by:livegirllove
ID: 11928889
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
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now