Hi everyone, this is my first post here!
I am making a small application that helps manage merchandise returns in a computer store I work for.
I am currently designing the database for it and an issue has arose which is probably obvious but is eluding me right now.
I have a bunch of tables (clients, inquiries, orders, etc...). An inquiry is when a client brings his defective product for replacement. In order to track products we sell we also store our orders in the same application (to make sure someone isn't bringing a product we didn't sell to him and to know if an ordered product was then replaced later). Each item we order has a serial number (which doesn't need to be unique since some companies use a general serial for a hole batch).
I did a rough beta about year ago of the app and the way it worked back then was that i had an orders table, storing relevant dates and info, and one column was a longtext and i would just store (in way similar to serialization) the hole order in it and then just get it back in queries and the app would reverse the serialization, hence giving me my order with all the appropriate serial numbers (the concept of a table in a longtext field...). I know that wasn't a good idea.... A bunch of problems ensued and made my life debugging the rough beta hell.
I am redesigning it now with better techniques i recently learned (DPs, DB modeling, etc...)
Ok, now you are in context, if you've read so far...
My problem is, how do I design the DB so my "orders" table can have a relation to another table named "products" that contains general information that is specific to the product (name, SKU (company product number), etc...) while at the same time store each specific serial number for each piece ordered in that order....
The only solution I come up with is just to make a product in the product table with that serial number but then how do i associate all those to the orders table and the product table will get pretty big pretty fast this way....
I use a MySQL 5.0 database
Thx for your time!