Help w/ table structure & advice on how to achieve ...

[bsamson]

Hello! I have a situation and I am struggling with how to achieve this ....

 

I maintain an internal site for the company I work for. There are 21 stores who use it daily to lookup contact info and submit company reports. All is working well. However, I am now in process of creating a new report and am having an issue with how to set it up.

 

Basically the process is:

 

A new customer signs up for new service (cell phone service.)

After new service is established the employee has to call the customer back in 15days, 45days, and 90days to address any concerns.

The employee visits the customer followup page where they enter basic customer info and submit the info.

I have already figured out how to generate the callback dates (taking into consideration no calls on sundays)

The current DB Tables stores the entrydate (in unix time)

 

Now, the situation ....

 

Should I create 3 fields (day15date, day45date, day90date) and store the callback dates, OR should I generate the callback dates based on entry date? If I go w/ option B, I would also create fields (day15status, day45status, day90status) that I would reference with a single letter (N-NEW, R-Reschuled, and C-Complete).

 

I have been pondering this situation for a week now and cannot seem to be able to come up with an efficient solution.

 

The whole reason for this report is to remind the employee when to call the customer. Of course taking into consideration if they fail to call them back on the due date to move it to the next day. I was thinking a cron job to achive that.

 

Anyway, thanks in advance for any suggestions!

 

[craygo]

you could create another table called callback and upon insert of customer create the 3 rows for the callback and link them to the id of the customer. That way you can have as many fields as you like to put in notes are whatever for each call. When you query the database you can search for all rows which have no data by using join statement.

 

Ray