The Area being Modelled is :-
Tracking Lost Racing Pigeons Big Data


From: Carlos De Sousa [mailto:cdesousa@orasage.co.za]
Sent: 07 May 2016 14:56
To: 'model_pls@databaseanswers.com'
Subject: Lost Racing Pigeons Database

Hi Barry,

I found your website interesting and decided to ask for your help.
Although I did do a data modelling course many years ago,  I never had the opportunity to apply the theory into practice.
Your assistance would be appreciated.

Here is the scenario: I want to develop a web site where stray racing pigeons can be reported and
reclaimed, in South Africa. I have a rough idea of what the ERD could look like,

however, I�m afraid that I may have missed the plot altogether.

The purpose of the web site is to identify who the breeder of the pigeon is and to contact them so
that they can reclaim and collect their pigeon.

The REPORTER of the stray pigeon could be a member of the public or a pigeon fancier � for simplicity
we�ll assume that all REPORTERs are members of the public.

Basically the structure of our racing pigeon community is as follows:

1. SANPO is the National Mother Organisation and amongst the many other things that SANPO do, they also
provide the unique ID rings that are fitted to the baby pigeons (These are life-long rings and will never
change for the life of the pigeon � Henceforth called �Liferings�).

SANPO also provides each racing pigeon fancier a unique SANPO ID Number.
Any fancier is South Africa who wants to partake in the sport/hobby of pigeon breeding, keeping or racing must
become a member of SANPO (Basically the equivalent of the Royal Pigeon Racing Association in the UK � RPRA).

By applying to SANPO for membership a unique SANPO ID is generated. There are three ways that SANPO supplies rings to the BREEDERS �
a.      Directly to the BREEDER (these rings only state a DATE (2 digit YEAR) and a NUMBER.
b.      Through a COMBINE (COMBINE is explained later). These rings have a unique federation ABREVIATION (not COMBINE abbreviation),
        2 digit YEAR and a NUMBER.
c.      Through the federation or union directly. These rings have the federation ABREVIATION, 2 digit YEAR and a NUMBER.

2.      For instance
a.      Above, the RING STEWARD for SANPO will have the list of all rings and the BREEDERs that they were supplied to.
b.      Above, the COMBINE RING STEWARD will have a list of all rings and the CLUBs the rings went to. The CLUB RING STEWARD will in
        turn have a list of which rings were supplied to which BREEDERs.

c.       Above, the FEDERATION RING STEWARD will have a list of which rings went to which CLUB and the CLUB RING STEWARD will have a
         list of which rings were sold to which BREEDER.

3.       Pigeons BREEDERS (fanciers) must be members of a CLUB (BREEDERS may be members of more than one CLUB).

4.       Groups of CLUBS form part of a regional/district FEDERATION or UNION.

5.       Some Federations join together (for competition purposes only � they maintain their own management committee) to form COMBINES.
         Where the FEDERATIONS are members of COMBINES, then the combines procure the Liferings from SANPO and in turn supply the rings
         to their Federations or Unions.
6.       Some Federations and Unions do not �Combine� and report directly to SANPO, where they also procure their Liferings.
7.       The Liferings have three groups of information as follows (Except for the rings supplied directly from SANPO that have only the YEAR and a NUMBER):

a.        A unique union or federation ABREVIATION,

b.       A 2 digit year of birth representing only the YEAR in which the Lifering was issued,

c.        An NUMBER, that although unique to that particular FEDERATION/UNION in that year of issue, may also be the NUMBER for another federation, or the
          same NUMBER for a different YEAR.
          So, to uniquely identify a pigeon by its lifering, one needs all three bits of information, the fed/union ABREVIATION, the YEAR of issue and the NUMBER.
          From this information one can identify who the breeder was (but not necessarily the current owner � as the pigeon may have been sold or given to someone
          else).           For example, a Lifering can be:
              i. TRPF 14 12345
             ii. GRPA 14 12345
            iii. NKPU 15 12345.
           Therefore it is possible that the NUMBER part of the Lifering can be used by another FEDERATION or in a different YEAR.

8.       It is currently very difficult and often frustrating for members of the public to trace the owner or breeder of a found/stray pigeon with a valid LIFERING.
         The hierarchy of unions, federations, clubs and combines is not very clear and is obviously very confusing for someone trying to do a �good deed�.
         Currently, to report a found pigeon (Perhaps I should emphasise that the reporting is for a FOUND pigeon.
         LOST pigeons are seldom reported by the owners/breeders) one needs to go to www.sanpo.co.za, go to the LOST PIGEONS tab, then access the RING REGISTER,
         look up the list of federations, unions and combines (hopefully the ABREVIATION on the ring will give some clue), etc, etc and these lists are never up to date anyway.
9.       Having finally established a RING STEWARD, then the REPORTER must contact them, most often by phone. Often the RING STEWARD will refer the reporter to
         another UNION or refer the REPORTER to the BREEDER, etc, etc. It just does not work, the way things are structured, and I�m hoping to solve this problem.

10.      I have sketched an ERD but am not clear on how to resolve the fact that the BREEDER can procure the Lifering directly from SANPO, from his/her CLUB or
         from his/her FEDERATION,  COMBINE or UNION, which in turn will determine which organisation and which ring steward will have the correct owner/breeder information.

11.   Please see attached WORD doc with a basic ERD for the above.

12.   Your assistance and comments will be much appreciated.

Should you have any questions or need clarifying on anything, please don�t hesitate to contact me.

Kind Regards

Carlos