Design-stage advice and opinions welcomed

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Ladies and Gentlemen,

I am tasked with creating a web-site for my local University of the Third  
Age (U3A) and am at the design-stage at present. The HTML and CSS are no  
problem and I have experience in coding a previous dynamic site using PHP/
MySQL. This post is cross-posted to c.d.mysql and c.l.php, both of which  
groups I have been active in (off and on) for some years. So I am NOT  
askimg you to do my homework :) I've set follow-up to the php group.

So far, I've neither created the database nor written a line of php code,  
I'm seeking advice/opinions before I start so as to avoid time-wasting  
mistakes in design. I'd be grateful for any comments on the ideas below.


At the moment there are 20 groups meeting at various venues at various  
dates and times which people need to know. Each group has a convener,  
people need to know his/her contact details too.

So for each group (typically they meet twice a month) I am thinking about  
the following 9 'columns'

autoincremented number
time of meeting
date of 1st meeting e.g. 'first tuesday'
ditto second
telephone of conv.
email of conv.

Some conveners chair two or more groups and more than one group may meet  
at any one venue.

I'm thinking I need at least three tables:


Is that enough normalization? I realize it's not fully normalized as more  
than one group may meet on the first Tuesday of the month but a table of  
dates seems to be over-egging the pudding? nobody is going to change the  
name of a day to the week?! opinions?


I am delightedly discovering the wonderfully versatile strtotime function  
which will simplify matters no end, so long as I store correct syntax in  
the database e.g. 'first Tuesday' rather than '1st tuesday' etc. Also,  
'of this month', 'of next/second month' and 'of third month' are going to  
be REALLY useful. Very cool!

Most of the php will be pretty straightforward - producing a list of  
meeting dates and times for a group (drop-down list of group populated  
from the groups table, etc). The challenge will be producing a complete  
dynamic 'diary' page where the next 100 day's meetings are listed in  
datetime order. I think I'll need a for or while loop but how do I avoid  
querying the database once or twice on each iteration??? Again I'm not  
asking for code but principles to work to, to avoid wasting resources.

Anyway, that's more than enough for the present. If you've read this far  
then thanks for your patience. Feel free to comment on any or all of it.

Thanks again,


Re: Design-stage advice and opinions welcomed

On Thu, 24 Sep 2015 10:35:48 +0000, Derek Turner wrote:

Quoted text here. Click to load it

Sorry, formatting lost by newsagent, perhaps some semicolons will help.

 autoincremented number; name; time of meeting; date of 1st meeting e.g.
 'first tuesday'; ditto second; venue; convener; telephone of conv.;
 email of conv.;

Re: Design-stage advice and opinions welcomed

On 24/09/15 11:35, Derek Turner wrote:
Quoted text here. Click to load it

I would add a table of actual events, containing the event date, or a  
value indicating when it takes place if its a regular thing, and  
pointers to a venue, convener and group in each event record.

So that data moves put of the group table.

That way a group can change venue date or convener for any given meeting.

Only you can say if that is something that may be usefiul however

Quoted text here. Click to load it

Global warming is the new Margaret Thatcher. There is no ill in the  
world it's not directly responsible for.

Re: Design-stage advice and opinions welcomed

[Ignoring the F'up2 comp.lang.php and setting F'up2 comp.databases.mysql as  
this is not PHP-specific so far]

Derek Turner wrote:

Quoted text here. Click to load it

Correct.  Consider drawing Entity-Relationship-Diagrams before you do  
anything else.  A correct model goes a long way towards a correctly working  
Quoted text here. Click to load it

You tell me.  Why is it important to normalize databases?

Quoted text here. Click to load it

You need to find a way to store the information “first Tuesday” without  
storing that string in the database.  Because in the Web application, for  
repeating venues you should only be reading one record per venue and  
calculate the rest as needed.  Also, what is stored in the database should  
not depend on the used programming language or the presentation of the data.

Quoted text here. Click to load it

Certainly.  Do not make “richard”’s mistakes.

Quoted text here. Click to load it

Parse error.

Still, simply storing trivial strings as dates is not a viable approach for  
an event calendar.  Can you think of a reason why?
Quoted text here. Click to load it

Hopefully you are not considering to write this from scratch, without any  
libraries or frameworks.

Quoted text here. Click to load it

Simple: you do not.  Make a query whose result contains all the information  
you need, and store the result (in a PHP data structure).

Zend Certified PHP Engineer
Twitter: @PointedEars2
Please do not cc me. / Bitte keine Kopien per E-Mail.

Re: Design-stage advice and opinions welcomed


Quoted text here. Click to load it

Personally, I'd definitely add a fourth table "meetings" with columns of  


a) You've used the phrase "typically" a future-proofing red flag alert  
that meetings might sometimes end up more than twice per month.  And  
might you have virtual groups that don't even have formal physical  

b) Will you ever need to check for group/venue/day clashes?  If so then  
cross checking that will include 1st meeting for one group against both  
1st and 2nd for another will become complex.

And for the "day", I'd consider defining it as a multi-column attribute  
of week-of-month and day-of-week so that they can be stored as just  
integer values, being mindful of regional differences in definition of  
DoW #1 being either Sunday of Monday.

I note in a followup posting that you might code for Xmas, etc. whereas  
I'd possibly recommend creating a "holidays" table as well, to cater for  
one-off venue closures (4th July?) and the moveable beast that is  


Site Timeline