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

Groups > comp.databases.mysql > #6040

Re: Design-stage advice and opinions welcomed

Cross-posted to 2 groups.

Followups directed to: comp.databases.mysql

Show all headers | View raw

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

Back to comp.databases.mysql | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Design-stage advice and opinions welcomed Derek Turner <frderek@suremail.je> - 2015-09-24 10:35 +0000
  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 Derek Turner <frderek@suremail.je> - 2015-09-24 21:15 +0000
      Re: Design-stage advice and opinions welcomed Jerry Stuckle <jstucklex@attglobal.net> - 2015-09-24 17:53 -0400
      Re: Design-stage advice and opinions welcomed Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2015-09-25 04:57 +0200
  Re: Design-stage advice and opinions welcomed Geoff Muldoon <geoff.muldoon@trap.gmail.com> - 2015-09-25 13:15 +1000
    Re: Design-stage advice and opinions welcomed gordonb.lizck@burditt.org (Gordon Burditt) - 2015-09-25 17:38 -0500
      Re: Design-stage advice and opinions welcomed Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2015-09-26 15:16 +0200
      Re: Design-stage advice and opinions welcomed Geoff Muldoon <geoff.muldoon@trap.gmail.com> - 2015-09-28 09:46 +1000
        Re: Design-stage advice and opinions welcomed Derek Turner <frderek@suremail.je> - 2015-09-28 03:34 +0000
      Re: Design-stage advice and opinions welcomed Derek Turner <frderek@suremail.je> - 2015-09-28 03:27 +0000
        Re: Design-stage advice and opinions welcomed Jerry Stuckle <jstucklex@attglobal.net> - 2015-09-27 23:53 -0400
          Re: Design-stage advice and opinions welcomed Derek Turner <frderek@suremail.je> - 2015-09-28 10:46 +0000
            Re: Design-stage advice and opinions welcomed The Natural Philosopher <tnp@invalid.invalid> - 2015-09-28 12:57 +0100
            Re: Design-stage advice and opinions welcomed Jerry Stuckle <jstucklex@attglobal.net> - 2015-09-28 09:41 -0400
              Re: Design-stage advice and opinions welcomed bill <william@TechServSys.com> - 2015-09-29 07:14 -0400

csiph-web