I'm somewhat new to db structuring and I have a fairly general question, but I don't know how to word it. Instead, here is a use case that demonstrates the question.
I'm preparing an inventory allocation database (MYSQL) that will be incorporated into a web UI employee front end, for the purposes of managing inventory. There is a SHIPMENTS table, each entry representing a shipment with a shipping address, tracking number, ship date, etc. I want to provide a way for an employee to create an itemized manifest that references a particular shipment and lists the items that are included in the shipment. Each manifest will have a variable but usually large amount of line items and each line item will have several different descriptive columns. It seems to me that I can't have a master MANIFESTS table with each entry being a manifest - there would be too many columns and they would have a hard limit. It also seems to me that creating a new table in my database every time a manifest is created (there will be thousands) can't be the way.
So the question for this particular use case is - Does MYSQL provide a structure to facilitate this process, or is this a matter of document generation within the scope of my PHP and HTML code? Keep in mind, these manifests will need to be queried and indexed just like the rest of the db.
I have a number of use cases that are along the same line, so I feel there must be some optimized way of doing this.
