Invoice table. Using a foreign key as a quantity/instance? ..i am lost here!

[artificialSteve]

I am making an invoice table for a business website, an employee logs in and selects from a list of items they used on a job, and then from a list of job types(to calculate the labor cost). All of the possible items and job types are stored in two seperate tables:

 

hardware table:

 

hardware_id int not null auto-inc.. pk

hardware_name    varchar(30)

hardware_cost    int(4)

 

jobs table:

 

job_id  int not null auto-inc.. pk

job_name    varchar(30)

job_cost      int(4)

 

THE PROBLEM: How do I make a new table for each new invoice that is submitted, using values from my two tables above? The reason why I want to do it this way is to avoid NULL entries. There can be anywhere from 1 to 16 options selected on the invoice.

Here is my psuedo-code(Yes, I am a n00b)

create table random_name

count the number of $_POST selections and create corresponding number of fieldnames (item1, item2, etc..)

insert into each field the value of post where value="foreign key id"

 

so the end result would simply show what items the employee used, what type of labor charge, and that would be the end of the whole thing!!

I really am having a hard time figuring out the best way to represent this idea, and it shouldn't be that big of a deal, right???

ps, thanks to the people who help out the ignorant.

 

 

[fenway]

Why are you making a new table for each anything?

[artificialSteve]

I actually was thinking too experimentally about it. I thought that tables could be used as one-time-containers for data that has reached the end of it's life-cycle. Well I think I am now on 'Level two', as the saying goes. I realized  that I can't think about database design the way my brain wants to, I must think about it the way it was designed to represent what my brain wants to see.

 

so anyway...some very helpful, straightforward advice from #PHP IRC chat revealed what endless hours of google, and even some 'PHP MYSQL' books DIDN'T(AHEM).

 

-use a primary key consisting of (invoice_id,job_id)  //I had no idea this was possible, yet it makes perfect sense

 

My problem was better stated as:

"You want to have the same cost_id on the same invoice more than once". 

 

Use one invoice table for each of "costs" and "products"

 

end up with 3 invoice tables:

 

one table: 'invoice'

 

containing the common data like:

 

(invoice_id, user, date, etc..)

 

then one table: - 'invoice_items_costs'

 

(invoice_id, cost_id)

 

then one table: 'invoice_items_products'

 

 

(invoice_id, products_id)

 

Anyway, this has sort of warped my mind(and that is a good thing)