MySQL multiple table query

Posted on 2012-09-18
Last Modified: 2012-10-02
I am trying to build a subscription service for a local charity which will allow users to access a service. The subscription is yearly contract that will also be a roll-on contract after the year unless the user cancels the service. The users can choose to pay for the service in a Monthly, Quarterly, Semi-Annually or Annually. A user may pay any number of months in advance and in some cases users may not pay for a number of months thus being overdue with more than 1 month. The table I have so far built is as follows:

  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `Service_id` int(11) NOT NULL,
  `custID` int(11) NOT NULL,
  `Pay_Term` int(11) NOT NULL,
  `Service_startDate` date NOT NULL,
  `Service_ExpiryDate` date NOT NULL,
  `Service_Charge` int(11) NOT NULL,
  `paid` int(4) NOT NULL,
  `balance_Remaining` int(11) NOT NULL,
  `payDate` date NOT NULL,
  `notes` text NOT NULL,
  PRIMARY KEY (`sid`)

Open in new window

I am more of a coder than a database guy so I will be grateful if someone can help me with the database structure.

Many thanks in advance.
Question by:aniga42
    LVL 12

    Accepted Solution

    Consider breaking this up into at least three tables:

    (A table to hold all info specific to the user themselves, doesn't contain any data that will change when the contract or payment changes

    A table to hold the details of the contract. Sign date, expiration date, contract number, pay term (if it's static over the life of the contract), etc...  when a contract expires, it can be extended as a new row with a new row ID

    Holds all debits and credits to the account. I would create this as a table with an identity/auto increment column,  a link to the contract and or user, a descriptive column describing the transaction (service charge, payment, scheduled invoice, etc... ), and then an amount column that lists the amount of the transaction in our descriptive column.

    Personally, since amounts due/overdue can be calculated, I would calculate them rather than store them. Others may opt for storing the data.

    The design will change given additional process and application details, so I've been intentionally vague. But this should be a good starting point.

    Author Closing Comment

    Thanks for trying though examples would have been beneficial

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now