Topic awaiting preservation: Dynamic Tables or the like in SQL (Page 1 of 1)

Ok I'm not exactly sure what I'm trying to say here so I'll try and say it the best way I know. I'm trying to create an inventory management system for our company. The total list of products and the like is fine with me I know how to do that. What is hard is that I want to record when a job goes out the door exactly what items and how many of each they have with them. The trick is that they we have over 500 different items we stock and they may only take out the door a total of 20 different items with them each time. I don't want to store in a table 480 blank spaces. I could code this in C with a linked list of linked lists basically.

I hope this is somewhat clear as to what I'm looking for. I believe MySQL supports it with Dynamic tables but I'm unsure how to approach it since the mysql site doesn't contain very useful information on it.

Thanks

I'd just make a table called order details or something with 3 cols

order_id, item_id, quantity

Then when an order goes in you make a new entry for each Item sold. You can then just query for all the rows with the same order id. And you'd have no blank columns.



.:[ Never resist a perfect moment ]:.

What happens if some items have more fields than just a quantity? Such as a motor would have a model # and serial number associated with it. It would be easy if everything was just a number and quantity. I guess I could create 2 tables then. 1 for the basic items and 1 for the more advanced.

I was really trying to get a single row per order instead of multiple rows with 1 item per row. I was reading on the dynamic tables and it sounds like they can have multiple varying columns per row. So you could have almost a comma delimited file then. Lets say

Order 1: has
ID: 101, quantity 2
ID: 102, quantity 3
ID: 501, quantity 1, M# 101, S#202

Order 2: has
ID: 503, quanity 10

Thus the first row would be
101,2:102,3:501,1,101,202:

and 2 would be
503,10:


Seems like a dynamic table setup like that would be faster on querying since you'd only have to find one. And insert and deletions would be easier and faster. And looking at the overall table in standard mode would be easier.

Doing it OrderID, itemID, Quantity would work but you would have millions of rows in a short time and thus searching after a bunch of time has passed would be a SERIOUS overhead.

Basically we pull out of the warehouse over 500 different items a day, but only 2-3 jobs a day. Thus on the first case you have a Order(N) search on 500 per day and on the 2nd you have a Order (N/2) on 3 per day.

It sounds like you're trying to do too much with one table
Generally the table I described would be a linking table with 2 more tables

1 for order and 1 for products. The products table would be where you describe the individual items, the order table would have the specifics of the order.



.:[ Never resist a perfect moment ]:.

Ya just discussed it a bunch with acidbox. I know I need a table for product description. I'm just figuring the best way to make the other table and waste as little space as possible and keep the searching and deletion and whatnot as fast as possible.