I'm trying to design a simple accounting database for my retail store, can somebody check if this model is stable enough to implement for queries and encoding.

Three tables
1.Company (clients)
2.Invoice (orders)
3.Payments (also contain credit memos for returns)

Fields
1.Company
-CompanyID
-CompanyName

2.Invoice
-InvoiceID (PK)
-CompanyID(FK)
-PaymentID(FK)
-InvoiceAmount
-InvoiceDate

3.Payment
-PaymentID (PK)
-InvoiceID (FK)
-CompanyID (FK)
-PaymentAmount
-PaymentDate

Payments can pay multiple invoices and Invoices can contain multiple payments

I want to be able to query a company's account status (e.g. how much does it still owe my store) by selecting the company from a drop down menu (the data for the drop down will be extracted from the CompanyName field), then inputting a range of dates. The results will display the company's transaction (orders/payments/returns) with my store for that range of dates, and it will also compute any remaining balance that company owes my store (e.g. orders - payments = balance)

There will also be a seperate page for encoding data for new orders and payments. I'll be using forms in html. I'm thinking that I have to select the company first, by drop down menu. Then I'll select wether to input payments or orders. After selecting, I'll just have to input the amount and date of the corresponding order or payment.

Can this be done with only three tables and with their respective fields?

Thank you very much for any help, info or insight provided

FYI, I am totally new at databases, I was just able to put this together by reading some free literature online. No programming experience either, but I could always learn that. Just point me to the right direction

I'm curious... There are literally thousands of accounting packages, many of which are quite good, all of which are cheaper in the long run than a "roll your own" system could be. Some of the accounting packages can even be customized.

Why on earth would you write your own package?

-PatP

Many to many relationships are broken down using an interim table which is made up of the primary keys of the two outer tables. Simple, eh?

Can you suggest anything that's not bloated? I'm a really really really small business, you can't even call it an enterprise. I just want something that is simple. Plus, I want to learn mysql and php

to georgev:

so you're saying that i need to change my two tables, namely the payments and invoice table, into one table? or do i retain the two and create another "interim" table?

thanks for the help guys

I've had really good luck with QuickBooks for companies between 1 and 50 employees with less than 2000 customers (for AR) and 2000 vendors (for AP). This fits the bill for pretty much anyone that does not have a true IT department.

About the only exceptions that I've seen are highly regulated businesses (pharmacies, very small cellular providers, accounting firms and highly specialized legal firms come to mind), and those are only due to very specific regulatory requirements. If you don't have to provide bizare reporting to some regulatory entity, don't sweat this point.

-PatP

I've always referred to the table used to define a Many-to-Many relationship as an intersection table.

By all means keep the two tables. add a third intersection table. it has at a minimum, two columns. These two columns are foreign keys, pointing to the primary keys of the original two tables. The intersection table's primary key is a compound primary key, which consists of BOTH the two foreign keys. In other words, the combination of (in your case) payments_id and invoice_id are unique, and neither are null.

Thanks Loquin

So now, I have 4 tables, will this be enough to get the database system working?

What is the purpose of having an intersection table? how will i be able to use them?

If I may - before you design a database to underpin your business please get a handle on relational database design 101:
http://www.tonymarston.net/php-mysql...se-design.html
http://r937.com/relational.html
The former is a guy that works with mysql and php. You'll love him

pootle flump ur codings are working excelent.

I'm trying really hard to understand these things about functional dependencies and multi-valued dependencies. can somebody explain them to me in layman's term? or am i doomed to just be a retailer my whole life without knowing how to program a database.

Or better yet? if there is somebody really really kind enough to just walk me through the steps in what I need to do to finish my database.

To pick up where we left off, loquin suggested that I create an intersection table from the tables, invoice and payments....

which brings me back to my question as to what is the purpose of having an intersection table? and how will i be able to use them on my query.

my query would be to check the account balances of my customers. The query will involve selecting a company, and then to input a range of dates. for querying a specific date, i will just input the same dates on the "from" and "until" forms. it will then display the company, it's invoices' amount and payments' amount, which are both on the same column. all the invoices' amount are added. the same with payments' amount. and then the total of the invoice amount will be subtracted by the total of the payments' amount.

hope somebody is really kind enough with time in their hands to help me with this.

Thank you for your patience.

the most frequently used names i've heard are relationship and association and even many-to-many table

i've also heard it called a junction table, but this is probably used only by database guys who worked on IDMS

The intersection/relationship/association table is used in order to model a many to many relationship.

One example of a many-to-many relationship is a track meet.

You have many events. You have many contestants. Any given event will have one or more contestants. Any given contestant can participate in zero or more events.

In modeling this situation, you would have a table to hold the events data (id, Description, Location, etc.) You would also have a table to hold the contestant data (id, last name, first name, etc.)

Now, to assign a contestant to an event, you would add the third, intersection table. The intersection table would contain the contestant ID and the event id. The intersection table's primary key would be BOTH of these two id's. This would uniquely define which contestants participate in which events. In addition, the intersection table's contestant_id would reference the contestant table id field, and the intersection table's event_id field would reference the event table id field.