Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.php > #15683 > unrolled thread

Design-stage advice and opinions welcomed

Back to article view | Back to comp.lang.php

Contents

  Design-stage advice and opinions welcomed Derek Turner <frderek@suremail.je> - 2015-09-24 10:35 +0000
    Re: Design-stage advice and opinions welcomed Derek Turner <frderek@suremail.je> - 2015-09-24 10:46 +0000
    Re: Design-stage advice and opinions welcomed The Natural Philosopher <tnp@invalid.invalid> - 2015-09-24 15:46 +0100
    Re: Design-stage advice and opinions welcomed Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2015-09-24 22:58 +0200
    Re: Design-stage advice and opinions welcomed Geoff Muldoon <geoff.muldoon@trap.gmail.com> - 2015-09-25 10:57 +1000

#15683 — Design-stage advice and opinions welcomed

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

[toc] | [next] | [standalone]

#15684

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

[toc] | [prev] | [next] | [standalone]

#15685

On 24/09/15 11:35, Derek Turner wrote:
> 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?
>

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.

[toc] | [prev] | [next] | [standalone]

#15686

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

[toc] | [prev] | [next] | [standalone]

#15687

In article <d6i203Frj62U1@mid.individual.net>, frderek@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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.php

csiph-web