Database design for Inventory & Billing

[anirban1087]

Hi,
I am working on a Inventory & Billing Management System. I am having some problem with the design of some tables. The problems are as follows

1. I have a product table. Where I am storing the product details, but the problem is that each product have an unique serial no, generated by the manufacturer. Now I want to store the serial number in my database. What should be my approach for this?

2. The second problem is with my billing section. I would like to save each and every bill generated from my system for future use. Now any bill may have a single item (single quantity)/ single item (multiple quantity)/ multiple items(single quantity)/multiple items (multiple quantity). Now I would like to have the serial number(s) also be included in the bill, for future reference. What would be the table structure for this?

Please help

[jbisono]

About the first question when you say by the manufacturer sounds like you are acquiring the product thru a purchase order right? if that is true then i will save the serial no in the purchase order line item. Note: what happened if you are ordering 10 pieces for the same product? does that means you have 10 different serial no's?

for the second question once you have your purchase order now you have your products with the serial no too.

let me know if i help you a little bit with this, maybe you can explain better.

[anirban1087]

Yes you are right. I can save the purchase order. In fact I am doing the same thing. But it is also true that 10 pieces of same product will come with 10 different serial number. Just think of any electronics goods. They all come with unique serial number. And I am right now unable to work that out.

[jbisono]

Ok i wanted to make sure we are in the same page :).

i did something similar but with the customer order process, anyway what about if you create another table with the Purchase_order, Line_No or Part_No (dont know how your table are set up) but basically for each purchase order -> part No you can have multiple serial No. then you will be able to enter as many serial no as you order qty.

this is how you can make it easier for the person who is entering the data, if you order 10 pieces and the manufacturer give this no as serial no sn100-001 after the dash is an incremental No. up to qty order then you can do some programming to generate 10 serial No. because is not funny have a order qty of 100 parts and entering that manually you have to find a convention how you can generate those serials no.

I have seen people using an excel sheet also to import that to the tables.

[anirban1087]

Hi,
Thanks for your prompt reply. I have also agree with you. May be this is the only solution to this kind of problems. Once again thanks a lot.

[swenning]

Also, it will usually be easier if you start by modeling the information (e.g. in the free tool RISE Editor) instead of the database. You can then later decide which environment you want to implement the solution in and generate the database and application layer.

[crishjeny]

in product table you can store manufacturer serial number and use as per your needs, no need to create another table

[jbisono]

The problem to create the serial number in the product table is that you can have multiple serial numbers for the same product, like previously discuss if you order 10 pieces of a product you will have serial No from, let's say PRODUCT-001 TO PRODUCT-010, all serials No refer to the same product.

and each time you buy the same product will have different serial No. again.