db structure question - HTML Forums

DamianWarS · 10-16-2009, 08:47 AM

I haven't done much work with dbs so I know I might be a little backward thinking so I thought I would ask the best way of making this db here.

i am making a db for an invoice program. aside from other things on the invoice it has an itemized list of the work done. There are no set "products" so each invoice will have different things and different prices and can't be predicted. So invoice 'A' could have 1 item and invoice 'B' could have 10 items and invoice 'C' could have 20 items. I want the entire invoice saved so since I don't know the number of items that will be in the invoice how to I structure the db to hold these values.

I thought about making dynamic tables that are created when a new invoice is saved. Then the table could hold as many rows as it wants and invoice 'A' would only have 1 row, invoice 'B' would only have 10 rows and so on. But that would mean I would have a new table for every invoice and I could end up with a lot of tables in my db.

So i thought about just making the table with more columns than would be ever needed in a given invoice to represent each item. Lets say I create 30 columns like this item0, item1, item2 ... item29. The problem with this is when I only have 1 item on the invoice there is a lot of wasted space and there is always the possibility that an invoice would exceed the limit I put

so I am interested in anyone's ideas on how to structure this the best way.

Vege · 10-16-2009, 01:01 PM

two tables

Quote:

create table invoice (
id int not null auto_increment,
name text,
who to bill text,
where to ship text,
watewa text)

Quote:

create table invoice_items(
invoice_id int,
item text);

Where invoice_id of invoice_items -table refers to ID of invoice -table
This way you can have 1 to n invoice_items per invoice.

Do i make any sense?

DamianWarS · 10-16-2009, 02:35 PM

As I mentioned before its been a while since I did anything with databases so sorry if I sound novice but I'm just writing this out to see if I understand what you are saying.

you created a table "invoice" with all the customer information in it and then you created an "invoice_item" table with all the items in it. The two tables are connected by the invoice's ID and invoice_item's invoice_id with a 1:many relationship.

so the idea is I have one invoice with many items on it so my items table may look something like this:

Code:

itemID	item	price	invoice_id
1	socks	5	1
2	shirt	10	1
3	tie	5	1
4	socks	10	2
5	shirt	10	2
6	socks	5	3

my real items are not as simple as socks and shirts but i'm just keeping it simple. So when I want to bring up my invoice again I select the table where invoiceID == ID and I get my invoice.

I think I understand it now and I think that will work thanks for pointing me in the right direction and please correct me if I'm wrong

Vege · 10-16-2009, 03:23 PM

I might be really wrong here.
"Invoice" as a term is new to me (had to google about it

)
But as i get it it's a list of ordered products?

Should it be so that meny items can belong to meny different invoices?
Some items might not belong to any invoices so adding invoice_id into items table might not be the best idea?

Meaning 3 tables:
ITEMS
itemID, item, price

ITEM2INVOICE (just connects items and invoices)
itemID,invoiceID

INVOICE
invoiceID invoice information

Meaning same product can be in multiple invoices

DamianWarS · 10-16-2009, 05:39 PM

i understand what you mean, and an invoice is just a receipt or itemized list with prices and totals. I see what you are doing with the 3 tables and trying to prevent putting the same item over and over again. for example something like this:

Code:

ITEMS TABLE
itemID	item	price
1	socks	5
2	shirt	10
3	tie	5

Code:

ITEM2INVOICE TABLE
itemID	invoiceID
1	1
2	1
3	1
1	2
2	2
1	3

Code:

INVOICE TABLE
invoiceID	name
1		bob
2		sally
3		jane

I think I have got it... thanks for your help.