Email 1) - April 18th
Hi Barry, I look after the data systems for Urdd Gobaith Cymru.
We are reviewing our database systems and looking for some assistance with modelling our data to
ensure we fully understand our options before deciding how best to develop our systems for the future.

Please could you let me know whether you might be able to help with this and an outline of costs?.
E.g. on-site/off-site consultation and your data modelling courses.
Alternatively any recommendations you might have re. suitable data modellers for our requirements.

 We are a youth charity organising activities for young people through the medium of Welsh and the
challenge on the data model and front-end systems is that we don�t fit neatly into any industry categories
and have aspects of travel agency, hotel / residential activity booking, sports competition organiser,
cultural competition organiser etc.
Also that we operate completely through the medium of Welsh with Welsh/English language selection on customer facing systems.

We have around 50,000 members between 8 and 25 yrs old from all around Wales who take part in a wide range of leisure, cultural
and sports activities and competitions, both for first and second language Welsh.

These events are organised by Urdd staff and youth officers at county and national level.

We have internally developed on-line registration and event management for many but not all our events, and an externally
developed and hosted system which handles our residential centres bookings and registration for a large sports event.
We would like to have a solid basis on which to consolidate and improve these systems for coming years for an improved
customer experience, more efficient internal event management by our central and regional organisers, more effective
communication and cross-marketing and improved compliance with data protection requirements. Our MySQL databases have
been developed incrementally over many years and we believe that review and optimisation of the data model for our
organisation is an essential pre-requisite to any further internal or external front end development.

While we should be able to carry out much of the detailed analysis and data modelling ourselves based on existing systems and
requirements we would appreciate input regarding our options for combining the top level overview of event, people and
participation data for both customers and management (analysis, communication, marketing and data maintenance etc) while
allowing for different detailed requirements and departmental processes for our different event types and complexities.

E.g. there is a lot of similarity in the structure of competitive events for Eisteddfod and sports, but Eisteddfod events have
much more detailed data requirements and have to link to external systems so may be best kept separate to allow independent development.

Similarly, residential events, trips etc have some similar/some different requirements to competitive events.


To give you some flavour of the detail:

Our event types include:
� residential courses at one of our 3 centres (booking system for groups and individuals, on-site/off-site activity scheduling)
�  a wide range of competitive sports (regional and national, team and individual) and regional sports clubs and taster events
� a varied range of annual Eisteddfod musical, dance, reciting, poetry, arts and crafts competitions (groups or individuals),
progressing from area through county to national level where we link with TV/radio companies systems (for on-screen results,
copyright clearance for chosen pieces, biogs for competitors and judges, presenter information etc.).
� regional events and trips with single or multiple payment.

Events are organised by regional and national Urdd staff and youth officers.

Urdd membership is required for most but not all events.
Our members are organised into branches which are mainly schools/colleges + some clubs.
The branches are organised regionally into area, county then region.
Many types of volunteers are associated with the events from branch leaders (mostly teachers), volunteer club leaders, sports
club leaders, sports referees, eisteddfod judges and regional and national commitees by area and subject.

Competitive events may require competitor/adjudicator lists, timetables by event, results management (input/output/display and
progression to next stage etc).

Trip and residential centre visits often involve bus pickups.

Many events require consent/health/dietary requirement information etc
Group and team and some individual competitions and events are registered by the branch(school) leaders. Parents and older
members can also register for individual competitions/events.
Membership can also be paid via the school/branch or increasingly by parents on-line and has a gift-aid option.
Sorry if this is too much detail initially but thought it best to give an outline of what�s involved!
Thanks

Lyndon Jones

Urdd Gobaith Cymru

Email 2) - April 18th
Bore da Barry, thanks very much for both your emails.
The approach sounds ideal for us and thanks very much for your help and pricing � very much appreciated � diolch yn fawr iawn.
I�ll confirm with my management but that sounds fine.

A tutorial approach as you suggest sounds very useful � by that I assume you mean a more interactive approach with more
discussion/explanation of steps? A
ny advice on our options for taking this forward  - migration to integrated data architecture - would be very much appreciated.
Diolch yn fawr
Lyndon

Email 3) - May 2nd.
Noswaith dda Barry,

Apologies for my slow response to your emails. We are entering a very busy period leading up to our major annual Eisteddfod event
throughout Whitsun week.
I�ve outlined some of what we�ll be doing over the next few weeks below * as it may help to give a flavour of how some of the data is used.
I�ve also attached a basic ER diagram I prepared a while ago showing the main elements of Eisteddfod and Membership parts of the Urdd system.
(The tables and fields themselves are in Welsh).
I�m thinking it might be useful to talk through this (and maybe also for a similar part of our regional/sports events data) to highlight some
of the more complex aspects of our event participation and how an improved data model might handle these?
This may speed up our communication of the requirements if that fits in with what you need but please let me know if you would prefer a
different approach.

Re availability, a phone discussion tomorrow or Tuesday would be possible for me if you�re available.
After that I may get some time during w/c 14th but to be fair to yourself I�ll only be able to give this the attention it deserves once the Eisteddfod
is out of the way, say w/c 11th June.

Thanks again for helping us with this.
It�s very much appreciated.
We are of course happy with the cost estimate you�ve given us and are happy to pay as one or several bills whichever works best for you.

BTW. not sure where your dictionary question fits in but there are a few excellent on-line welsh lexical and translation dictionaries.
This is a good list:    http://www.welsh-dictionary.ac.uk/online-welsh-dictionaries/

Re welsh language software, there is Bangor Universities Cysgliad package which combines dictionary/thesaurus/spell checker and its main
use as a grammar and mutation checker (the main problem for welsh speakers and learners!) which integrates into Word.

Microsoft have been a great help by providing a welsh language interface for both Windows and Office.
Google and Mozilla Facebook and many others also have Welsh language options with volunteers providing the translations.

I also teach Welsh to adults part-time and there are a number of excellent on-line sites including �Say Something In Welsh� https://www.saysomethingin.com/welsh/course1  and
Duo Lingo https://www.duolingo.com/course/cy/en/Learn-Welsh-Online

Diolch yn fawr iawn
Lyndon
-------------------------------------------------------------------------------------------------------------------------------------

*Winners from 17 �county� level Eisteddfod (themselves winners of area level eisteddfodau) go through to the national event, competing as individuals,
small parties or large groups, choirs/orchestras etc.
These have to go through prelims on the day which will select 3 to go to the main stage. I�ve just spent a couple of days, with a mix of automated and
manual procedures, organising the prelim timetables for competitors from each school/branch to avoid clashes as best we can.

These are all on-line for each school/branch to view. As enrolment is all on-line we could make even more use of the competitor level data to improve
the timetabling etc.
Next week we will be adjudicating our arts and crafts event, again with the winning items from all �county� levels being collected from all over wales
to a large warehouse/show area � to be grouped by competition for adjudication by a team of judges, 2 for each category, assisted by local volunteers
and national arts commitee members.

We take photos of all 1,2,3 place entries � which go through to an arts exhibition during the Eisteddfod week.
The rest of the itmes being driven back and redistributed to the schools and to young people.
Photos and results go on-line and to TV for use during commercial breaks in the eisteddfod week etc.
To minimise errors, all picture ID and descriptions are typed into the central database as the photos are taken.
All entries are listed from the database, checked in from the vans by region, assembled and again checked by competition number before adjudication,
results entered into the database, then new lists for winning and non-winning entries to be ticked off before being taken away.

Unlike our residential centres which operate all year round, the eisteddfod parts of the system get used once a year and are developed incrementally
although there are some similarities to other competitive events such as our sports competitions. Up to and during the national eisteddfod week, when I�m not
looking after the actual data, myself and 2 more IT staff set up and maintain the network, laptops etc for staff, volunteers,
data input and output etc and liaise with the TV/radio teams using the data and resolve any issues with the real time results to TV graphics/web/social media etc.

Diolch yn fawr iawn
Lyndon

BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB

notes for barry 13/6/18 to develop the initial simple 'members -: booking :- events' model
EVENTS AND BOOKINGS - clearly the 'heart' of the system and central to most of what we do and customer experience and detailed staff admin/event management/reporting and analysis requirements at many levels
(while we have data structures and systems in place for much of this, they have evolved over time and are used in different ways for different activities.
So worth looking at / remodelling from top down level to see how far they can potentially be combined and optimised and improved for our requiremetnts
before comparing with current structures and planning future development)
Notes as follows:
1)	an event booking or registration form will be completed by a parent/adult member or teacher/branch leader or Urdd staff on their behalf
2)	For competitive events (individual or team sports, individual or team eisteddfod competitions) an event will be associated with one or many competitions.
        These may be group or individual competitions
3)	For group competitive events (and group visits to residential centres) a school/branch may book a group of children/members or one or more sports teams or
        dance teams, musical parties or choirs etc to a competition and create bookings for several competitions at a time, with an appropriate school
        teacher/branch leaders contact details for use before and during the event.
        Depending on the event we may alow them to go back to the entry/booking 'forms' to add or remove names from a group or sports team up until near the event.
           Booking forms may perhaps be regarded at two levels (both for booking and reporting/marketing)- we need to know a school/branch is taking part in an event or         competition as early as poss, at the latest by closing date of event registration - we might allow changes to names on forms/individual members taking part/members of eisteddfod group/residential visit/sports team until nearer the event or even not require names at all until a day or so before the event e.g. names in sports teams. Similarly we need to measure/track participation at a school/branch level and also at individual member level.
4)	These competitions (eisteddfod and sport) form part of an annual syllabus which is repeated for each urdd county (Rhanbarth) and often area (Cylch) (a sub-division of county to which a school or community branch belongs). This 'syllabus' or range of available activities will include details such as competition id, name, category, age or school yr range allowed to take part (from/to), whether individul or team competition, min max numbers per team, what winners will go through e.g. 1=1st only, 2=2nd only. In the case of eisteddfod competitions they will also define many details of set pieces for that year. Events in any region may include part of the available syllabus (range of sports or eisteddfod competitions) depending on demand and local preferences. e.g. one region may hold 7-a side football for one age group on a different day (event) or put them all in the same event, similarly eisteddfod competitions may be grouped in several different ways between different events, e.g. age or catgory or detailed type of competition to suit judges etc
5)	The range of sporting competitions/eisteddfod syllabus is offered annually with some gradual changes and it would be useful to track participation/popularity across years (while this is most important for competitions, in reality most of our events are repeated annually)
6)	Participation in some (most?) events requires membership on or before registration, many events then being free to members, others require payment, some require multiple payments. (Main exceptions to this at present are sports clubs, sports taster/fun events organised by sports dept and residential centre visits organised directly by schools rather than local urdd organiser. This could change to extend membership requirement.)
There are also a very few non-core ad-hoc events in which non-members or even adults can take part - could be handled separately if needed.
7)	Many events require health form, special diet/special needs etc/ behaviour agreement, additional documentation for overseas trips such as copy of passport , copy of EHIC - mostly on paper at present)
8)	Sports clubs and urdd community clubs could be regarded as repeating events (e.g. weekly/monthly during term time) but may be better regarded as a single event with multiple sessions, attendance currently recorded manually but should be on-line to aid production of regular participation figures. This is an essential and burdensome task for sports clubs who have to meet and report on detailed targets for each type of event and group (for a range of governmental and sports organisation partners).
9)	Staff and volunteers will also be associated with events and specific competitions in a range of roles (e.g. judge/ref/timekeeper/steward) Need to maintain database of eisteddfod judges/ refs/coaches by expert area matching category of competitions - e.g. instrumental music/netball etc) and which events/dates they are invited/assigned to.
10)	events and competitions have different types of lists to manage all aspects of the event - including the below aspects
11)	Competitions may have prelims, which may have a formal or randomised order of performance - eg swimming and athletic competitions have to be allocated lanes with as many prelim races as reqd. for available number of lanes. Competitions have results for competitors, 1st, 2nd, 3rd etc, (+ other statuses DQ/not up to standard/no-one competing etc which would ideally be included on results lists if required), competitive event winner will progress from one event to another together maintaining links to all relevant details (leaders contact details) possibly health etc, in the case of eisteddfod competitions, selected piece info etc *. National 'finals' will be organised by the Urdd Sports or Eisteddfod dept. (Results entry,  result lists at each stage (pdf and on-line), certificates in various ordering etc). Ideally winners of a particular competition on the syllabus could be easily tracked / listed over multiple years (not currently easy to do).
12)	Each competition in a sports/eisteddfod event may have program information such as order or more specific info on time, location etc. This may extend to competitor order/lanes/prelim (individual times) to be included on published or on-line/information screen event programs and competitor timetables
13)	Eisteddfod competitions at national  level have detailed status info for managment and tracking of competitor, competition prelim and main competition status and results release during the event. Some of these could be of use at the regional level.
14)	Eisteddfod competitions have additional linked data for media coverage: competitor/judge biogs and others
15)	While sports and eisteddfod competitions share a lot of similar structure - there are also differences to consider when deciding how far they can share the same data structures.
16)	Sports competitions will often be in the form of series of games between two teams requiring various types of draws, play-offs etc and scoring rules and rules for progression of winners between rounds. Athletics and swimming will have lanes/prelim races, time/distance recording, (time also recorded for competitors in some eisteddfod competitions to ensure not exceeding time limit).
17)	requirement to allow easy booking and single payment by parents. for several children and/or several related events e.g. a range of half-term events in a particular area.
18)	A few events have paid extras when booking such as bus pickup, extra activity.
19)	members belong to a school or urdd sommunity branch (CANGEN), the branch belongs to an area (cylch) which belongs to an urdd county (RHANBARTH - urdd staff team at this level) and region (talaith - managment only). Note sports dept / officer regions are different.
20)	Note residential events might need to be treated as two related events � i) in the centres booking system the urdd regional dev officer may be regarded as the paying customer bringing various groups to the centre at a particular price per child

ii) In the regional dev officers event, the customers are the schools who pay another price per child including cost of buses/regional staff and admin etc, this is the event which will be advertised to schools.

schools collect money from parents some in installments to spread cost - all children attending via regional urdd dev officers have to be members so also pay membership if not already joined.
Note for me: (LJ to think/consult about whether any changes worth considering in recording both school and community branch and maintain records of non-members in schools for some events - e.g. in the small proportion of members joining via community branch we won't have a link to their school - this would be useful also when enrolling for eisteddfod competitions - look at impact on current practice of allowing to select from other branches while registering for competitions) Also individual members, currently handled as a fake branch for the area (cylch) - although works well in practice.)
DIMENSIONAL MODEL for reports + communication/marketing
we require detailed analysis of our membership as shown - (note link of member to cangen linked to cylch linked to rhanbarth linked to talaith as above). add analysis of whether joined thru parent/school/urdd staff, payment method, family discount, GIFT AID, contact permission given, and be able to compare across years. [As per events, membership is either registered and paid by parent/older members, school/branch leader (in small or large batches= on-line membership form) or by urdd staff on their behalf ]
in addition to this we also need to analyse and compare branch (cangen/cylch/rhanbarth) and individual participation in EVENTS also by event type, individual competition, age group and compare across years
-----------------------------------------------AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
QUESTIONS AND ANSWERS - June 13th. 2018
Q1 � what is likely relationship of dimensional model / reporting system to main database in our case?
� is it likely to be best implemented as a separate database updated from the first or as I suspect this will depend on how divided or integrated the main model/database ends up?

Q2 - what did you have in mind re. �reporting suite� � are there any you would recommend not directly tied to a
specific implemention like dynamics/microsoft BI would be? - currently we can generate specific reports from the
system (alpha anywhere) - admin lists to manage events + summary reports of participation, also use MyDBR to create detailed cross-tab reports from mysql database showing membership by age and region, age and gender by
region, age and language by region etc and participation in eisteddfod and other competitions by individual
competition and other criteria. Similarly are you aware of any suitable general purpose communication/marketing suite enabling segmenting /crossmarketing using these dimensional criteria??

Q3 � you asked about Financials  - what info would help/what sort of document did you have in mind? I�ve outlined some points below
- the link to the annual report on page 1 of requirements doc. I sent shows a financial overview of the urdd,
- paid events currently paid by paypal analysed by event code lined to organiser and dept (might be useful to
include cost code in event or event category).
System needs to be able to clearly analyse event payments down to individual events and customer (with easy refund options etc).
Payments made by cheque or cash will be banked against (dept/regional/category) finance code.
Financial contribution from membership analysed by county (Rhanbarth) and region of Wales (talaith).
- residential centres currently manually raise individual invoices for deposit and final payment for group bookings via Sage 200 system -
possibility of raising this on database system and feeding into Sage. Manageable number of large value invoices at present.
(LJ to consult re. value vs effort/cost and likelihood of requirement to put all finance activity thru Sage or other financial system at
some point??)