Design-stage advice and opinions welcomed

[Derek Turner]

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.

DATABASE:

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
name
time of meeting
date of 1st meeting e.g. 'first tuesday'
ditto second
venue
convener
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:

groups
venues
conveners

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?

PHP:

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,

Derek

[Derek Turner]

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

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

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.;

[The Natural Philosopher]

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

> PHP:
>
> 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,
>
> Derek
>

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

[Thomas 'PointedEars' Lahn]

[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:

> DATABASE:
>
> 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.
>

> […]

> I'm thinking I need at least three tables:
>
> groups
> venues
> conveners

Correct. Consider drawing Entity-Relationship-Diagrams before you do
anything else. A correct model goes a long way towards a correctly working
application.

> Is that enough normalization?

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

> I realize it's not fully normalized as more than one group may meet on the
> first Tuesday of the month

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.

> but a table of dates seems to be over-egging the pudding?

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

> nobody is going to change the name of a day to the week?! opinions?

Parse error.

> PHP:
>
> 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!

Still, simply storing trivial strings as dates is not a viable approach for
an event calendar. Can you think of a reason why?

> 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).

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

> 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???

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

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

[Geoff Muldoon]

In article <d6i203...@mid.individual.net>, frd...@suremail.je
says...

> I'm thinking I need at least three tables:
>
> groups
> venues
> conveners
>
> 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?

Personally, I'd definitely add a fourth table "meetings" with columns of
group/venue/day.

Why:

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
meetings?

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
Easter.

GM