October 8, 2008 - 11:20pm
I tend to go with separate
I tend to go with separate tables - one for invoices (id, customer id, date, etc) and one for invoice details (line number, invoice id, item id, quantity, price, etc). This gives you the dimensions you need without too a rigid structure. You could serialize the invoice details and store as a single field of the invoice table, but then you have to weigh the cost of converting the details to and from the serialized data.
Without knowing what data will populate the tables, it is difficult to suggest a best approach. Try it both ways and add some real data to see how each performs.
October 9, 2008 - 11:59am
skenow @ Oct 8 2008,
October 13, 2008 - 2:50pm
Paul Steinbrueck @ Oct
I tend to go with separate tables - one for invoices (id, customer id, date, etc) and one for invoice details (line number, invoice id, item id, quantity, price, etc).
That's the approach I would suggest too.
- Paul
Mr. Dos,
As skenow and Paul suggested, this is probably the best way to go... The model of storing your data in seperate tables and building them as you need is called Database Normalization. Being normal is good /smile.gif" style="vertical-align:middle" emoid=":)" border="0" alt="smile.gif" /> Being Sixth Normal Form is better.
Hi all,
I'm trying to build myself a simple customer management system in PHP/MySQL for creating and tracking projects, customers and invoices, and I'm just wondering how y'all would store invoice items in the database. It would be simple enough but for the fact I want to have a flexible number of items on the invoice instead of just a list, e.g.:
+----------+----------------------------------------+-----+-----------+------------+
| FOOBAR1 | The first example item. | 1 | $98.70 | $98.70 |
| FOOBAR27 | Another example item. | 3 | $76.92 | $230.76 |
(Item total would obviously be automatically generated.) Dumping everything into a text field in the database (items pipe delineated or something) and splitting them up with PHP when parsing the invoice is one obvious path, but I'd like something a little more elegant than that. Having a table just for invoice items with a row for each item is another thought that crossed my mind and I might go that route, but that strikes me as being awfully spread out (and potentially inefficient). Anyway, your thoughts would be very appreciated!
--- Mr. DOS