Personal Project Help

[mahdoum]

Hello everyone,

I currently manage hosting for a number of customers and started created a system to manage the customers and their hosting accounts / domains. I have the following setup currently:

Tables
-Customers (Customer name and contact info)
-Customer Accounts (An account links domains + hosting in one account)
-Domains (A list of all domains)
-Hosting Accounts (Account info such as user name, server IP etc)
-Hosting Packages (A list of package details (BW/disk space) and prices)

I want to add to this database a way of creating and keeping track of invoices per month with the following restrictions:
1. A customer may buy a domain in advance for 3 years, in the above database design there is no way to distinguish how long a domain was paid for, and check when payment is due.
2. Currently there is no way of adding billing cycles i.e. when to create the invoice.

I'm trying to make the database flexible to allow customers to login and see what accounts they have, and when payments are due.

Any help pointers to a better database design would greatly be appreciated.

Sorry in advance if anything is unclear

Cheers

[asaukani]

Hello everyone,

I currently manage hosting for a number of customers and started created a system to manage the customers and their hosting accounts / domains. I have the following setup currently:

Tables
-Customers (Customer name and contact info)
-Customer Accounts (An account links domains + hosting in one account)
-Domains (A list of all domains)
-Hosting Accounts (Account info such as user name, server IP etc)
-Hosting Packages (A list of package details (BW/disk space) and prices)

I want to add to this database a way of creating and keeping track of invoices per month with the following restrictions:
1. A customer may buy a domain in advance for 3 years, in the above database design there is no way to distinguish how long a domain was paid for, and check when payment is due.
2. Currently there is no way of adding billing cycles i.e. when to create the invoice.

I'm trying to make the database flexible to allow customers to login and see what accounts they have, and when payments are due.

Any help pointers to a better database design would greatly be appreciated.

Sorry in advance if anything is unclear

Cheers

i suggest u create tables : CustomerContractHdr and CustomerContractDtl
Hdr contains fields:
- customer id
- contract number
- length of contract
- date of contract
- total amount
- type of payment :monthly or yearly basis
- status of contract
dtl contains:
- due date
- amount due
- status of payment
using this way when customer sign the contract u build hdr record and build dtl record for each due date
for billing cycle what u have to do is to check at the beginning of the month or everyday as needed which customer is due
U also can develop the program to accept input customer id and display the current contract status
i hope it can help

[mahdoum]

i suggest u create tables : CustomerContractHdr and CustomerContractDtl
Hdr contains fields:
- customer id
- contract number
- length of contract
- date of contract
- total amount
- type of payment :monthly or yearly basis
- status of contract
dtl contains:
- due date
- amount due
- status of payment
using this way when customer sign the contract u build hdr record and build dtl record for each due date
for billing cycle what u have to do is to check at the beginning of the month or everyday as needed which customer is due
U also can develop the program to accept input customer id and display the current contract status
i hope it can help

Thanks for your help, the dtl table kind of helps. Let me try to explain my problem in a better way.

The CustomerAccounts table I have allows me to relate a certain hosting package (that has the price) and domain names (that each have a price). The problem here is the following, a customer may want to pay for a domain name 3 years in advance, but pay for the hosting each year so for example an invoice would look like the following:

1st year: hosting price + 3 years domain price
2nd year: hosting price
3rd year: hosting price
4th year: hosting price + domain price (3 years, 1 year etc depending on what customer wants again)

So my problem is how to show and track certain domains that have been paid for x years, and when their next billable cycle is etc.. taking into consideration that the CustomerAccounts is where related data is placed.

Cheers

[asaukani]

Thanks for your help, the dtl table kind of helps. Let me try to explain my problem in a better way.

The CustomerAccounts table I have allows me to relate a certain hosting package (that has the price) and domain names (that each have a price). The problem here is the following, a customer may want to pay for a domain name 3 years in advance, but pay for the hosting each year so for example an invoice would look like the following:

1st year: hosting price + 3 years domain price
2nd year: hosting price
3rd year: hosting price
4th year: hosting price + domain price (3 years, 1 year etc depending on what customer wants again)

So my problem is how to show and track certain domains that have been paid for x years, and when their next billable cycle is etc.. taking into consideration that the CustomerAccounts is where related data is placed.

Cheers

u can choose two possibilities:
- make different type of hosting for the customer who pay domain annually and who pay 3 years in advanced and develop dtl record according to each type
another solution is to make domain accounts separate from hosting account but still use the same dtl record
Personally i prefer the second solution so the dtl record will add payment type wether it is for domain or hosting
by checking dtl record u can test for each customer which billing is already paid, when the next due date
if u check my design when customer sign the contract the whole payment record is generated, so if the customer pay domain for 3 years and pay hosting annually 1 dtl record is generated for domain flagged as paid, 3 dtl record generated for hosting 1 flagged as paid another two are not, but it has due date
U can write the program to check the status of customer payment
hope it helps