Problem Designing Tables

[DieHard]

hello,

i have two invoices..

1st invoice is Receipt:

receipt invoice with , autonumber , CustomerName , Receipt No , Items_Sold , Qty , TheirSerialNo , Purchase Date , Today Date ,

2nd invoice is Delivery:

autonumber , CustomerName , Receipt No , Delivery No , Items_Sold , Qty , TheirSerialNo , Purchase Date , Today Date

i tried to desing tables but i failed , how many tables do i need?

[kaihirst]

Hi,

Ive designed stuff like thsi and do this all the time. youre looking at something like this.

Invoice


InvoiceNO (auto generated number, NOT NULL, INT 11)
OrderDate (DATETIME NOTNULL) default value (GETDATE(NOW))
ContractNo (VARCHAR 100, NOT NULL)
above could be (BIGINT 100, NOT NULL) if only numberical
Compnay name (VARCHAR 200, NOT NULL)
Address1 (Varchar 100, NOT NULL)
Address2 (VARCHAR 100, NOT NULL)
City (VARCHAR 50, NOT NULL)
State (VARCHAR 50 NOt NULL)
Zip (VARCHAR 50, NOT NULL)
Tel (BIGINT 100, NOT NULL)
CtctName (VARCHAR 100, NOT NULL)
DelAddr1 (VARCHAR 100, ALLOW NULL)
DelAddr2 (VARCHAR 100, ALLOW NULL)
City (VARCHAR, 50, ALLOW NULL)
State (VARCHAR 50 ALLOW NULL)
DelZip (VARCHAR 50, ALLOW NULL)
DelTel (BIGINT 100, ALLOW NULL)
DelCtctNAme ( VARCHAR 100, ALLOW NULL)

This takes care of the delivery and office addresses, now for the itemisation

PartNo (VARCHAR 100, NOT NULL)
above could be again (BIGINT 100, NOT NULL)
Description (VARCHAR 254, NOT NULL)
ItemCount (BIGINT 100, NOT NULL)
Nett (BIGINT 100, NOT NULL)
Gross (BIGINT 100, NOT NULL)
VAT (BIGINT 100, NOT NULL) default value ((NETT/100) * 17.5)


And thats it. this table will now provide any company with a single invoicing reporting system.All youhave to do is write the report layout and code the query
then you have one table that wil manage all the company stock and invoicing details.

If you want payment due dates and such, just go ahead and add them using a Date conversion method that is specific to the database being used for this table to convert the order date to the payment date.

Hope this helps

Kai

[szlamany]

If it's possible to have several deliveries to satisfy an order, you might want to consider more of a "transaction" based table. More flexible (less flat) then what you are suggesting.

Here's a couple of threads that have dealt with issues like this before:

http://www.vbforums.com/showthread.p...stock+quantity

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

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

They might be more stock/quantity related, but the concept is similar...