Opinions pls as I start 2nd build: data dictionary and how to do randomized schedules

[shomer]

Pls feel free to ignore this. This is a very wooly question and on StackOverflow I would be flamed to death!

This is a BIG ask as I am being very vague which I know is annoying.

I am building a system for therapists to be able to send therapeutic texts to patients. I have a basic version up and running using php flie_put and file_get with a gazillion text files. Works fine but obviously not scalable.

Now I want to make a system for 10 therapists all using the same database (final version will have one db per therapist for security).

I am getting myself a bit brain scrambled on the data dictionary and handling randomized dates.

On the data dictionary could someone just force me to come to a firmer naming structure. I seem to be all over the place and once I feel I nail down one part another veers around. (I have put it at the end and NB nearly all varchar are (25) at the moment. They will be scaled appropriately later on.)

SECONDLY RANDOMIZED MESSAGING

It is annoying to get the same email from you therapist at the same time every day.

The desire of the therapists is getting a bit complicated. I came up with three linked schedules that would for example offer a message Weds, Fri and Sat at 11 am and Mon, Tues at 2pm and Sunday every two hours between 9 am and 9pm.

Even doing that gets very messy very fast. There is also a big desire for some sorts of randomization. I got so confused I mapped it out as a dummy form on Excel (pls see below).

Question 1 Does anything like this exist in jQuery or anywhere?

What I am coming up with is HORRIBLE and I hope someone out there has faced a similar task and come up with a good solution.

So this would be one of three linked forms.

Simple input would just be the top part. The three forms would all run at the same time. Say form one running for Mon and Weds days in the morning, form two for Wed and Fri afternoon then form three running for other days fixed days and times. Using start and stop dates the effects can be appear quite “randomized” (I am tempted to increase the number of schedule options.)

The more complicated part adds multiple messages a day and multiple times. The three forms could cascade with form one running for 3 days the form two for 4 days then form three running for x days and back to form 1 etc. If they cascade
there can be an initial start date but then the options have to run for xx days or it gets too complicated.

FINALLY - STORING THE SCHEDULE vs MAKING THE SCHEDULE

The scheduling is becoming horribly complex.In the first version I ran a CRON job every five mins to see if a message needed sending.

I am tempted to make a message stack that gets built every few hours and updated whenever a schedule is changed.

It will be a long time before this system has 100 therapists using the system and each therapist is unlikely to have more than about 10-15 patients wanting to receive texts so I am guessing a maximum of about 10,000 messages a day is unlikely to be hit for ages. I do want to build it as sensibly as poss in the first place but keep going back and forward between checking every five minute to see if any patient needs a message sending (ie checking all of the message instances - (the linked message/client/schedule nexus) for every patient) OR whether I should build an output stack and just run through that.

Thanks for any thoughts.

Finally here is the “data dictionary” in rough form.

			Naming rules: FK "id"s become tableid eg clientid.			
																	
																	
	Table	Description	Type	Keys etc							
	Field 				        				              	
							                              					
	THERAPIST				              					
				                     		                      					
	id					smallint								
	therapist_name				varchar(25)	Unique	
	first					varchar(25)								
	last					varchar(25)		
	initials				varchar(25)			
	phone_number				varchar(25)							
	phone_type				varchar(25)				
	country_code				tinyint						
?	time_zone	CURRENT TZ		varchar(25)	default pac/hon
x	email					varchar(25)								
	status		Paid for etc		varchar(25)							
	status2	Spare													
																	
	CLIENT							
													
	id					smallint								
	client_name				varchar(25)	Unique						
	therapistid				smallint	key with above						
	first					varchar(25)					
	last					varchar(25)					
	initials				varchar(25)		
	phone_number				varchar(25)		
	phone_type				varchar(25)	
	country_code				tinyint					
?	time_zone				varchar(25)	default pac/hon
x	email					varchar(25)				
	travelling				varchar(25)	
	crisis_status				varchar(25)	
		
											
	MESSAGE														
	id		smallint				
x	text		varchar(25)	
	image		binary												
						
									
	MESSAGE_GROUP		(group names unique for each therapist)												
	id			smallint					
x	group_name		varchar(25)	Unique		
	therapistid		smallint	key with above			
																
	MESSAGE_INSTANCE														
	id???			smallint	
	clientid		smallint	FK	
	messageid		smallint	FK
	groupid			smallint	FK/NULL
															
															
																	
	SCHEDULE_INSTANCE_1	PREPARED_SCHEDULE	
	id				smallint 				
	msginstid			smallint		messinstid										
x	days	smtwhfa format*		varchar(25)		schedinstid????
	start_date			date		*Thurs=h ans Sat=a		
	stop_date			date					
	expired				boolean			
	start_time			time				
	stop_time			time				
	period	Minutes between texts	smallint		
	numoftimes			smallint		
	reminder			date			
	linktoschedule			tinyint	
* days of week by one letter can be multiple days
															
															
	SCHEDULE_INSTANCE_2														
	id														
	msginstid														
x	days														
	start_date														
	stop_date														
	expired														
	start_time														
	stop_time														
	period	Minutes between texts													
	reminder														
	linktoschedule														
															
	SCHEDULE_INSTANCE_3														
	id														
	msginstid														
x	days														
	start_date														
	stop_date														
	expired														
	start_time														
	stop_time														
	period	Minutes between texts													
	reminder														
	linktoschedule														
															
	SEND_LIST	(one for EVERY msg due to go out in next xx )													
	id														
	messinstid														
	day														
	time														
															
	SENT_MESSAGES														
	id														
	text														
	number														
	time														
	day														
	timestamp														
	return_code																

[r937]

any time you have three nearly identical tables with names that end in _1, _2, _3… that’s likely a design error

also, regarding naming convention – watch out for names which can easily be keywords, like TEXT, NUMBER, TIME, DAY, etc.

[shomer]

@r937

any time you have three nearly identical tables with names that end in _1, _2, _3… that’s likely a design error

also, regarding naming convention – watch out for names which can easily be keywords, like TEXT, NUMBER, TIME, DAY, etc.

Thanks for the thoughts - esp the keyword observation.

The design is very “not right” but I find it useful to design as much as possible, then build and then, and only then, can I usually see some obvious **** ups. I always have 100 ideas and they just fall all over each other

Oh and just checked out your personal site (anything to avoid working ) . The Rubiks cube event was sweet and quite amazing. Just realized you are both Limebacks - he a relative?

[oddz]

I think you’re over thinking this unless there is something I missed. All you really need is a calendar type system with event scheduling that dispatches text using a cron that executes at a certain interval. I’m sure this is reasonable to accomplish using any common CMS and some custom code. I’m very familiar with Drupal so I would use Drupal alongside a calendar/scheduling plugin then fill in the rest with custom code. By no means a simple thing to accomplish but I wouldn’t think to difficult either. Putting that aside what you really need to build is a scheduling system.

[r937]

[ot]

@shomer

• he a relative?

yes, the youngest of my 4 children (he’s 21 now)[/ot]

[shomer]

@r937

Off Topic:

yes, the youngest of my 4 children (he’s 21 now)

Proud dad or what!!!

The blindfolded thing was UNBELIEVABLE!

What’s he doing now???

[shomer]

@oddz

I think you’re over thinking this unless there is something I missed. All you really need is a calendar type system with event scheduling that dispatches text using a cron that executes at a certain interval. I’m sure this is reasonable to accomplish using any common CMS and some custom code. I’m very familiar with Drupal so I would use Drupal alongside a calendar/scheduling plugin then fill in the rest with custom code. By no means a simple thing to accomplish but I wouldn’t think to difficult either. Putting that aside what you really need to build is a scheduling system.

I’ll have a look at Drupal and you are right the guts of this IS a schedulking system but it needs to be super, super flexible.

And BTW your sig made me LOLL (laugh out loud literally)

[oddz]

I’m not necessarily telling you to use Drupal. Drupal has a steep learning curve. I’m just saying this could be accomplished in Drupal. Therefore, it is likely it could be accomplished with many other platforms as well. That said learning Drupal and having clients embrace it isn’t a bad thing.

[shomer]

OK sorta new problem.

All my work for the foreseeable future is in Hawaii.

Ideally I would like my MySQL server set ot Pacific/Honolulu but I can’t (GoDaddy shared account.)

If I store DATE fields with real dates I think I will be screwed as things like NOW() will get horribly complicated.

Would I be better in just storing time and date in strings and then do the work in PHP? It would seem to lead to less confusion but somehow it offends me!

[shomer]

@shomer

OK sorta new problem.

All my work for the foreseeable future is in Hawaii.

Ideally I would like my MySQL server set ot Pacific/Honolulu but I can’t (GoDaddy shared account.)

If I store DATE fields with real dates I think I will be screwed as things like NOW() will get horribly complicated.

Would I be better in just storing time and date in strings and then do the work in PHP? It would seem to lead to less confusion but somehow it offends me!

Forget that. Think I just go with TIME and DATE formats.

Thanks

[r937]

consider DATETIME using UTC

[shomer]

@r937

consider DATETIME using UTC

Rudy thanks but no thanks. I will store in DATE and TIME and DATETIME as Hawaiian time.

UTC conversions would have to be done all over the place. For me, it will be easier just to use the DATE field as a well formatted string that I can perform operations on (if you see what I mean - I believe I can do thing like +2 days).

Everything else can be easily done in PHP.

If I have to keep going from UTC to Honolulu time life will get a lot more complicated for me.

Also if people travel then I just have to change one value and their 9am wake up call will come at 9am without ANY complicated math.

May not be perfect but that is how I plan to do it. This is not an industry grade build just an OK self build demo.

MORE IMPORTANTLY what has happened to super Rubiks Cube boy. I am in awe of his ability. Has the NSA gobbled him up yet? (And yes I do know u are in Canada)

[SpacePhoenix]

SET time_zone = Pacific/Honolulu;

Give this query a go, use it before the app runs any other queries

[shomer]

@SpacePhoenix

SET time_zone = Pacific/Honolulu;

Give this query a go, use it before the app runs any other queries

App… APPPPPP… I write programs m’dear but you are from Poole so you are no doubt young and affluent and of the fiendish smart phone generation.

Sadly your idea is a non starter:-(

"#1298 - Unknown or incorrect time zone: ‘Pacific/Honolulu’ " They have deliberately not installed the TZ database so all TZ functions (except a numeric offset) are screwed

But thanks for the punt anyhow.

I can fudge it all in PHP just a bit of a pain.