[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

MySQL multiple table query

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.
1 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.
aniga42Author Commented:
Thanks for trying though examples would have been beneficial

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now