How Many Table I Need To Do......

[DieHard]

thx for your help guys

yah each product has a unique serial!..., for that it depend on quantity

if quantity of a product is 25 piece = we will need 25 serial number

tblSerial (SerialId, ProdId, Quantity, SerialNumber)

here , we have to make the quantity 1 for each product!!!!

because qty and serial in the same table....

i think we need another table for serials..

[szlamany]

I'm not sure I follow that:

If you have PRODID=123 for example and need 25 different QUANITIES, then you fill:

tblSerial (SerialId, ProdId, Quantity, SerialNumber) 

            101       123       1       SNABC1
            102       123       2       SNABC2
            103       123       3       SNABC3
            104       123       4       SNABC4
            105       123       5       SNABC5
.
.
.
            121       123      21       SNABC21
            122       123      22       SNABC22
            123       123      23       SNABC23
            124       123      24       SNABC24
            125       123      25       SNABC25

So there is an arbitrary SERIALID - that relates to a particular PRODID+QUANTITY combination. In that table is stored the SERIALNUMBER.

In a way, the SERIALID is not needed - the primary key of this table could be the PRODID+QUANTITY combination. That makes a nice key in MS SQL 2000 when it's CLUSTERED, as all the QUANTITY info about a particular PRODID is in a single cluster on the disk (since the rows are so small), so I/O to retrieve info is very fast.

[brad jones]

So the answer to the original question seems to be.....


4


<and yes, I'm being coy and wasting a 1/10th of a second's worth of bandwidth, but the question did simply ask for a number.>

[FunkyDexter]

Customer - (CustID, etc)
Order - (OrderID, CustID, etc)
Item - (Item_Serial_No, OrderID, ProductID, etc)
Product - (ProductID, etc)

So a product might be "Widget". An Item might be "Widget1". And an order might have entries for "Widget1", "Widget2", "Widget3" etc.

You won't need a quantity on the order table because it will be a count of Items where ProductID = "Widget"

[DieHard]

Thx alot FunkyDexter , i think its the right way...

i have changed something to display quantities...., like

customers (ID, CustomerName)
Products (ID , ProductName)
Invoice (ID,CustomerID,PurchaseDate,DateIn)
Invoice Record (ID , QTY , InvoiceID, ProductID)
record details (ID , RecordID , SerialNumber)

thats will work too

but the problem , how to retrieve all DATA for Invoice #1 ?
i think it needs many joins

[FunkyDexter]

select * from invoice i
inner join invoice_record ir
on i.ID = ir.InvoiceID
inner join record_detail rd
on ir.ID = rd.RecordID
inner join product p
on p.ID = ir.ProductID
where i.invoiceID = 'the invoice youre looking for'