Page 1 of 1

PHP/MySQL - Yearly Versions

Posted: Thu Jan 28, 2010 12:14 am
by drewrockshard
This may or may not be a mySQL question, however, I believe it is both database specific and PHP specific.

I have a pretty elaborate question here, about how to have yearly versions of data retreival from mysql, as a PHP application.

I'm going to have a year drop down on my main application. What I'm wanting to know is what the best approach to display data based on the current year, and then offer old data for previous years.

For example, let's say, year 2009. At the end of the 2009 year, I decide to go through my customer accounts, and I know know that 10 of my customers are no longer going to be a customer, however I also know that I'll get new business in the coming year. How would I be able to then make these changes, and then move onto the next year, for instance 2010, without those customer, but be able to go back to that year and edit information (such as if they have an outstanding balance, and I'm just updating information related to that year)?

Another example is (keeping in mind of the previous information), I add a new customer in 2010; I only want that to show for 2010 (and coming years, if that customer decides to stay as a customer). I do not want the "cancelled" customer from the previous year to be in 2010, and I do not want the new 2010 customer to be in the previous year. So, basically, using one database, how can I have multiple "versions" or "years" implemented into one database.

This is more of a technical AND dba design question. I already have the database structure and everything, but I'll probably need to update the schema after discussing this, as I'm sure that the database structure might need a little bit of changing after this.

Please let me know :)

Re: PHP/MySQL - Yearly Versions

Posted: Thu Jan 28, 2010 4:07 am
by Eran
You should start by posting you current database schema as well as describing what data is contains. Your question is not very clear, but perhaps it will become clearer after we see the database structure.

Re: PHP/MySQL - Yearly Versions

Posted: Thu Jan 28, 2010 11:07 am
by drewrockshard
pytrin,

Here is my schema in all its glory.

Code: Select all

 
******** Database (2)='gclbeta' ********
    $result2= 'Resource id #43'
 
. Table (0)='assignedcompany'
Descriptions:   Field   Type    Null    Key     Default     Extra
. . Column (0)=     AssignedCompanyID   varchar(255)    YES             
. . Column (1)=     AssignedCompany     varchar(255)    YES             
 
 
. Table (1)='billingtype'
Descriptions:   Field   Type    Null    Key     Default     Extra
. . Column (0)=     BillingTypeID   int(11)     YES             
. . Column (1)=     BillingType     varchar(50)     YES             
 
 
. Table (2)='cctype'
Descriptions:   Field   Type    Null    Key     Default     Extra
. . Column (0)=     CreditCardTypeID    int(11)     YES             
. . Column (1)=     CreditCardType  varchar(50)     YES             
 
 
. Table (3)='crew'
Descriptions:   Field   Type    Null    Key     Default     Extra
. . Column (0)=     CrewID  varchar(50)     YES             
. . Column (1)=     CrewLocation    varchar(50)     YES             
 
 
. Table (4)='customercc'
Descriptions:   Field   Type    Null    Key     Default     Extra
. . Column (0)=     CustomerCCID    int(11)     NO  PRI         auto_increment
. . Column (1)=     CustomerID  int(11)     YES             
. . Column (2)=     CreditCardTypeID    int(11)     YES             
. . Column (3)=     CustomerCCNumber    varchar(20)     YES             
. . Column (4)=     CustomerCCVerification  varchar(255)    YES             
. . Column (5)=     CustomerCCExpire    varchar(50)     YES             
. . Column (6)=     CustomerCCBilling   varchar(50)     YES             
. . Column (7)=     CustomerCCAutoBill  varchar(255)    YES             
 
 
. Table (5)='customernotes'
Descriptions:   Field   Type    Null    Key     Default     Extra
. . Column (0)=     CustomerNotesID     int(11)     NO  PRI         auto_increment
. . Column (1)=     CustomerID  int(11)     YES             
. . Column (2)=     NoteDate    datetime    YES             
. . Column (3)=     NoteDescription     varchar(255)    YES             
. . Column (4)=     NoteInitials    varchar(255)    YES             
 
 
. Table (6)='customers'
Descriptions:   Field   Type    Null    Key     Default     Extra
. . Column (0)=     CustomerID  int(11)     NO  PRI         auto_increment
. . Column (1)=     CustomerMapsco  varchar(10)     YES             
. . Column (2)=     CustomerActive  varchar(255)    YES             
. . Column (3)=     CustomerFirstName   varchar(50)     YES             
. . Column (4)=     CustomerLastName    varchar(50)     YES             
. . Column (5)=     Customer2FirstName  varchar(50)     YES             
. . Column (6)=     Customer2LastName   varchar(50)     YES             
. . Column (7)=     CustomerServiceStreet   varchar(50)     YES             
. . Column (8)=     CustomerServiceCity     varchar(50)     YES             
. . Column (9)=     CustomerServiceState    varchar(2)  YES             
. . Column (10)=    CustomerServiceZip  varchar(12)     YES             
. . Column (11)=    CustomerBillingStreet   varchar(50)     YES             
. . Column (12)=    CustomerBillingCity     varchar(50)     YES             
. . Column (13)=    CustomerBillingState    varchar(2)  YES             
. . Column (14)=    CustomerBillingZip  varchar(12)     YES             
. . Column (15)=    CustomerHomePhone   varchar(50)     YES             
. . Column (16)=    CustomerCellPhone   varchar(50)     YES             
. . Column (17)=    CustomerWorkPhone   varchar(50)     YES             
. . Column (18)=    CustomerWorkExt     varchar(7)  YES             
. . Column (19)=    CustomerFax     varchar(50)     YES             
. . Column (20)=    CustomerEmail   varchar(50)     YES             
. . Column (21)=    CustomerDog     varchar(255)    YES             
. . Column (22)=    CrewID  varchar(50)     YES             
. . Column (23)=    ScheduleID  int(11)     YES             
. . Column (24)=    DawnCustomer    varchar(255)    YES             
. . Column (25)=    CustomerNotes   mediumtext  YES             
. . Column (26)=    AssignedCompanyID   varchar(255)    YES             
. . Column (27)=    FuelSurcharge   varchar(255)    YES             
. . Column (28)=    Lentz   varchar(255)    YES             
 
 
. Table (7)='customerservice'
Descriptions:   Field   Type    Null    Key     Default     Extra
. . Column (0)=     CustomerSvcID   int(11)     YES             
. . Column (1)=     CustomerID  int(11)     YES             
. . Column (2)=     ServiceTypeID   int(11)     YES             
. . Column (3)=     FrequencyTypeID     int(11)     YES             
. . Column (4)=     DayID   int(11)     YES             
. . Column (5)=     CustomerSvcCost     double  YES             
. . Column (6)=     CustomerSvcBeginDate    varchar(40)     YES             
. . Column (7)=     CustomerSvcEndDate  varchar(40)     YES             
 
 
. Table (8)='customerservicedate'
Descriptions:   Field   Type    Null    Key     Default     Extra
. . Column (0)=     ServiceDateID   int(11)     YES             
. . Column (1)=     CustomerID  int(11)     YES             
. . Column (2)=     ServiceDate     varchar(40)     YES             
. . Column (3)=     ServiceAmount   double  YES             
. . Column (4)=     ServiceProvided     varchar(255)    YES             
. . Column (5)=     Fertilize   varchar(255)    YES             
. . Column (6)=     ServicePaid     varchar(255)    YES             
. . Column (7)=     ServicePaidDate     varchar(40)     YES             
. . Column (8)=     PaymentTypeID   int(11)     YES             
. . Column (9)=     ServiceNote     mediumtext  YES             
 
 
. Table (9)='day'
Descriptions:   Field   Type    Null    Key     Default     Extra
. . Column (0)=     DayID   int(11)     YES             
. . Column (1)=     DayOfWeek   varchar(50)     YES             
 
 
. Table (10)='emplogs'
Descriptions:   Field   Type    Null    Key     Default     Extra
. . Column (0)=     logid   int(11)     NO  PRI         auto_increment
. . Column (1)=     date    varchar(20)     NO          
. . Column (2)=     empid   varchar(20)     NO          
. . Column (3)=     note    varchar(100)    NO          
 
 
. Table (11)='employee'
Descriptions:   Field   Type    Null    Key     Default     Extra
. . Column (0)=     EmployeeID  int(11)     NO  PRI         auto_increment
. . Column (1)=     EmployeeGroup   varchar(10)     NO      user    
. . Column (2)=     status  varchar(15)     NO          
. . Column (3)=     USERNAME    varchar(20)     NO          
. . Column (4)=     PASSWORD    varchar(60)     NO          
. . Column (5)=     INITIALS    varchar(4)  NO          
. . Column (6)=     EmployeeFirstName   varchar(50)     YES             
. . Column (7)=     EmployeeLastName    varchar(50)     YES             
. . Column (8)=     EmployeeTitle   varchar(50)     YES             
. . Column (9)=     EmployeeSSNumber    varchar(50)     YES             
. . Column (10)=    EmployeeStreet  varchar(50)     YES             
. . Column (11)=    EmployeeCity    varchar(50)     YES             
. . Column (12)=    EmployeeState   varchar(2)  YES             
. . Column (13)=    EmployeeZip     varchar(50)     YES             
. . Column (14)=    EmployeeHomePhone   varchar(50)     YES             
. . Column (15)=    EmployeeCellPhone   varchar(50)     YES             
. . Column (16)=    EmployeeDLNumber    varchar(50)     YES             
. . Column (17)=    EmployeeDLState     varchar(2)  YES             
. . Column (18)=    EmployeePhoto   mediumblob  YES             
. . Column (19)=    EmployeeNotes   mediumtext  YES             
. . Column (20)=    CrewID  varchar(50)     YES             
 
 
. Table (12)='frequency'
Descriptions:   Field   Type    Null    Key     Default     Extra
. . Column (0)=     FrequencyTypeID     int(11)     YES             
. . Column (1)=     FrequencyType   varchar(50)     YES             
 
 
. Table (13)='options'
Descriptions:   Field   Type    Null    Key     Default     Extra
. . Column (0)=     YearID  int(4)  YES             
 
 
. Table (14)='pasteerrors'
Descriptions:   Field   Type    Null    Key     Default     Extra
. . Column (0)=     ServiceDate     varchar(40)     YES             
. . Column (1)=     ServiceAmount   double  YES             
. . Column (2)=     ServiceProvided     bit(1)  YES             
. . Column (3)=     ServicePaid     bit(1)  YES             
. . Column (4)=     ServicePaidDate     varchar(40)     YES             
. . Column (5)=     PaymentTypeID   varchar(255)    YES             
. . Column (6)=     ServiceNote     mediumtext  YES             
 
 
. Table (15)='paymenttype'
Descriptions:   Field   Type    Null    Key     Default     Extra
. . Column (0)=     PaymentTypeID   int(11)     YES             
. . Column (1)=     Payment Type    varchar(50)     YES             
 
 
. Table (16)='servicetype'
Descriptions:   Field   Type    Null    Key     Default     Extra
. . Column (0)=     ServiceTypeID   int(11)     YES             
. . Column (1)=     ServiceType     varchar(50)     YES             
 
 
. Table (17)='year'
Descriptions:   Field   Type    Null    Key     Default     Extra
. . Column (0)=     YearID  int(11)     NO  PRI         auto_increment
. . Column (1)=     Year    int(4)  YES             
 
 
I know above output isn't the prettiest.

Overall, what I'm wanting to do is at the end of each year, I would like to close out the year and start a new year automatically and auto fill the currently active customers for the new year since we will "bring" the new customers over to the new year. I'm going to have a drop down menu on the main page for the user to select difference years. I'm wanting to know how to get the logic inside the mysql database and work from there. Right now, I don't think i need too much help with the PHP, but more with the mySQL logic of this.

Re: PHP/MySQL - Yearly Versions

Posted: Thu Jan 28, 2010 11:23 am
by Eran
What part of your costumers is time based? what does "bringing over" means in this respect? what defines an 'active' costumer?

Re: PHP/MySQL - Yearly Versions

Posted: Thu Jan 28, 2010 1:14 pm
by drewrockshard
"Bringing over" means that any customers that are currently active, will still be customers in the next year.

An active customer is a customer that is currently having an "active" status:

Customers table:

Code: Select all

. . Column (2)=     CustomerActive  varchar(255)    YES
I don't understand what you mean by time based customers. You have a year - Jan 1st - Dec 31st = 1 year; that's how I'm calculating the year.