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:

> 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: _at_PointedEars2
Please do not cc me. / Bitte keine Kopien per E-Mail.