artificialSteve Posted April 8, 2008 Share Posted April 8, 2008 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. Link to comment https://forums.phpfreaks.com/topic/100113-invoice-table-using-a-foreign-key-as-a-quantityinstance-i-am-lost-here/ Share on other sites More sharing options...
fenway Posted April 8, 2008 Share Posted April 8, 2008 Why are you making a new table for each anything? Link to comment https://forums.phpfreaks.com/topic/100113-invoice-table-using-a-foreign-key-as-a-quantityinstance-i-am-lost-here/#findComment-511979 Share on other sites More sharing options...
artificialSteve Posted April 8, 2008 Author Share Posted April 8, 2008 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) Link to comment https://forums.phpfreaks.com/topic/100113-invoice-table-using-a-foreign-key-as-a-quantityinstance-i-am-lost-here/#findComment-512015 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.