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

[DieHard]

How Many Table Do I Need To Do The Following


Num...Customer.....Goods_Sold.....Quantity.....Serial_Number

example

Num.....Customer....Goods_Sold....Quantity....Serial_Number
==============================================

1..............ALI ........CD ROM ............4 ..........6541
.................................................................9879
.................................................................8989
.................................................................3200
...........................Floppy Drive .......1 ..........8989A0
-----------------------------------------------------------

Note That a Customer May Have Many Products Purchased
Note That The Serials Depend on Quantity , Each Product Has A Serial

How Many Tables Do I need To Design This Invoice?
Thx.

[szlamany]

Look at post #3 in this thread:

http://www.vbforums.com/showthread.php?t=373162

[edit] That thread is you - did you take this long to come back to the question??

[DieHard]

i got no specific answer until now
so i wanted to print them ,,,, maybe they can understand what do i need exactly , so they can help me

[szlamany]

i got no specific answer until now
so i wanted to print them ,,,, maybe they can understand what do i need exactly , so they can help me

Does post #3 in that other thread give you some ideas?

I would have a CUSTOMER TABLE - with CUSTNUM and CUSTNAME and other demographic data.

I would have a product table - with SERIAL_NUMBER and a description of the product (along with other product related data).

Then I would have some kind of transaction table to describe the order and the resolution of that order - that other post has links to other threads on similar subjects.

[DieHard]

plz! stop posting back , if u dont want to help

now ppl would think that i got the answer , while i DIDNT


still - UN RESOLVED -

[sebs]

He just told you what to do !

You should be nice with the people that tries to help you, y'a know

[si_the_geek]

plz! stop posting back , if u dont want to help

now ppl would think that i got the answer , while i DIDNT

What is wrong with the answer he gave? Your question was "How Many Tables Do I need To Design This Invoice?".

szlamany not only posted a good answer, but an explanation of what the tables should contain.


If you get an answer on any forum that isn't appropriate for you (for whatever reason), you should explain why - we aren't mind readers.

Being rude to one of the people that can help you best certainly isn't the way to get a resolution to your issue.

[Jacob Roman]

plz! stop posting back , if u dont want to help

now ppl would think that i got the answer , while i DIDNT


still - UN RESOLVED -

The only way people are going to know that this thread has been resolved is if you had went up to Thread Tools and clicked on Mark Thread Resolved. It will put a green check mark next to your thread and also put [RESOLVED] next to your thread title, like so:

[RESOLVED]How Many Table I Need To Do......

Programming problems aren't always solved by one post. And it certainly doesn't make people think the threads resolved. It normally will take many posts from many people, and even then, there's a slight chance your problems solved. Usually it won't be solved at all. Also, like the others have been saying, being rude will get you no help at all, and you will sometimes get negative ratings in your post from other people as a result. Hopefully this helps you out on how VBForums works. I'd help you with the database stuff, but I'm more of a game programmer than anything.

[DieHard]

guys! , im not yelling....
and thx alot for ppl that helped me
but his answer doesnt solve my problem

my problem is with SerialNumber that depends on Qty.

i think this invoice needs 5 ir 6 tables , but i cant arrange them,,.

thx all. - no offense

[zubairkhan]

We are here to solve problem each other, if you feel that some one's reply did not solve your problem you should explain in good manner. Look at no of posts szlaamy posted in this forum. If you still want help you have to return back your words and ask gentlemanly.

[dglienna]

He's been designing databases for a lot longer than you've been alive. If you have a question about his post, you should just ask him. He would have explained things to you.

[DieHard]

dglienna,zubairkhan

your posts , just to comment not to help
you have inceased the following

1- host bandwidth
2- host space
3- post replies

and wasted my time

why is that?

no offense.

[zubairkhan]

plz! stop posting back , if u dont want to help

now ppl would think that i got the answer , while i DIDNT


still - UN RESOLVED

guys! , im not yelling....
and thx alot for ppl that helped me
but his answer doesnt solve my problem

my problem is with SerialNumber that depends on Qty.

i think this invoice needs 5 ir 6 tables , but i cant arrange them,,.

thx all. - no offense

Look at your own replies in sequence do they help any one to solve the problem, what dgliena asked, he said eplain or ask your problem again.
i think now we should stop this, and should comeback on topitc.
What do you mean by SerialNo depend upon quantity. Do each product have unique serial no?. Or each record in invoice has unique serial no?.

[alva]

What do you mean by SerialNo depend upon quantity. Do each product have unique serial no?. Or each record in invoice has unique serial no?.

Indeed...

What you need is some kind of "Orders" table where each order whill have a unique ID.

Something like:

tblCustomer: ( CustomerID, CustomerName)
tblProduct: ( ProductID, ProductName, Price)
tblOrder: ( OrderID, CustomerID, ProductID, Quantity)

It's abit strange to have a unique order ID for every product though.
You should reconsider that ( eventually you'll see why because you will run into problems) and add something like "ItemPurchase".

tblCustomer: ( CustomerID, CustomerName)
tblProduct: ( ProductID, ProductName, Price)
tblOrder: (OrderID, CustomerID )
ItemPurchase: ( OrderID, GoodsID, Quantity)

[szlamany]

Does this reflect your requirements?

If not, please answer the points that I labeled with "right?".

tblCustomer (CustId, CustName) ... obvious table - right?
tblProduct (ProdId, ProdName) ... obvious table - right?


tblSerial (SerialId, ProdId, Quantity, SerialNumber)

...the above table satisfies your need to have
...a Product+Quantity have a unique SERIALNUMBER
...You could have SerailNumber as the KEY, and avoid SERIALID


tblOrder (OrderId, CustId, SerialId)

... maybe date ordered, status of order and so on
... This table only needs the CustId and SerialId to know
... the product and quantity - right?

Let's get the basic layout of tables and keys arranged before we start loading up the other columns you might need.

[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'