Results 1 to 11 of 11
  1. #1
    Join Date
    May 2007
    Posts
    7

    Help on Accounting Database Modelling

    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

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Payments can pay multiple invoices and Invoices can contain multiple payments
    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?
    George
    Home | Blog

  4. #4
    Join Date
    May 2007
    Posts
    7
    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

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  6. #6
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by paloyme
    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?
    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.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  7. #7
    Join Date
    May 2007
    Posts
    7
    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?

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    May 2007
    Posts
    7

    My head is spinning from reading tony marston

    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.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by loquin
    I've always referred to the table used to define a Many-to-Many relationship as an intersection table.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    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.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •