Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2005
    Posts
    15

    Do I need Relational Database ?

    Good morning
    I have been asked to set up a simple database system but not sure if i require relational or not.
    I have been asked to record racing pigeons results.
    I would have details of the bird itself ie ring number, then i would have the owner details then race details recorded every Sat.
    Not sure if I need 3 tables or just the one.
    Thanks for any assistance.

  2. #2
    Join Date
    Dec 2015
    Posts
    12
    This all depends on how much data you expect to enter into the database, and what variety of information you need to get out.

    If the needs are not great, it could be simpler to use a single table, each record with all the data in. This would be easier to maintain, quicker to handle, but would involve a fair bit of extra duplication.

    As you suggest, you could use three tables, i.e. one for the birds, each including a link to the separate table of owners, the table of owners, and the table of races/results with each record needing just a link to the indiv birds, and the indiv owners. This would save on data entry, and storage, and could allow more flexible reporting, but would be easier to mess up and damage links esp if you need to get someone less experienced to enter data.

    The relational variant would, of course, be more 'educational' for your development as a programmer. However, it may take longer tp develop/debug.

    If I was doing it, I'd go with the relational variant.

    Geoff

  3. #3
    Join Date
    Oct 2005
    Posts
    15
    Ok thanks thats a great help. I will have a go at both systems and see what happens. As long as it could be searched and maybe all races listed on a certain month my certain owners or by bird that would do. Thanks again.

  4. #4
    Join Date
    Aug 2017
    Posts
    12
    In addition to Geoff's reply you should also consider the data integrity. Will the same person be entering the data all the time? If it's all in one table it is very important that they enter the repeated fields exactly the same every month. If one week they fill in 'Pigeon1' and the next week they fill in 'Pigeon 1' or ' Pigeon1' that extra space will cause the database to assume that they are 2 different pigeons. If you don't maintain a table for the birds and reference it you will run the risk of getting muddy data that you will have to spend time cleaning up on a regular basis.

  5. #5
    Join Date
    Oct 2005
    Posts
    15
    Thank you all for all the help. I am a wee bit rusty as I used to do database programming many years ago with db2 etc. Could you just remind me about something, I am brushing up reading books. I will have 3 tables then 1 for the owner, 1 for the races and 1 for the bird. Each bird has a unique ring number on their legs. Wouldthis be the link between all the tables. Bird table so to speak would have ring number and owners name. Owners table would have ring number of their bird and name club area etc. Race number would have the race location date etc. What would be the link between them all? I thought ring number as it is unique. Sorry for basic questions.

  6. #6
    Join Date
    Aug 2017
    Posts
    12
    The owners may end up owning more than 1 bird (maybe one passes away and they will get a new one, etc). So I would not store bird info in the owner table. Owner would have a unique id (possibly auto generated) and their info. The Birds table will include an owner id as a foreign key and any bird info. The Race table will have the bird id as a foreign key and any other race data.

  7. #7
    Join Date
    Oct 2005
    Posts
    15
    Getting slightly mixed up have had a go. Do each table still require a uniqe key as well as a foreign key? I have a trial of dbforge is this the best to use as getting a wee bit muddled. Thanks.

  8. #8
    Join Date
    Aug 2017
    Posts
    12
    I generally prefer to have IDs of some kind. Preferably sourced from the data (like the ring_num on the birds) but I will make one up with identity (serial) columns if otherwise not available. However if it's strictly a table to work out a many to many relationship (like race_birds below) then I don't generally add one (instead a composite primary key or a composite unique index could be used here).

    I would do something like this myself (happens to be based on Postgres syntax, modify for your db of choice):

    CREATE TABLE owners (
    ownerid serial PRIMARY KEY,--identity, etc. auto increment column here depending on db chosen
    first_name varchar(100)
    --etc...
    );

    CREATE TABLE birds (
    ring_num integer PRIMARY KEY,
    ownerid integer,
    descr varchar(100)
    --etc...
    );

    CREATE TABLE races(
    raceid serial PRIMARY KEY,
    race_date timestamp
    --location, etc...
    );

    CREATE TABLE race_birds(
    raceid integer,
    ring_num integer
    --finish placement info, etc
    );

    ALTER TABLE birds ADD CONSTRAINT fk_birds_owner FOREIGN KEY (ownerid)
    REFERENCES owners(ownerid);

    ALTER TABLE race_birds ADD CONSTRAINT fk_races_birds FOREIGN KEY (ring_num)
    REFERENCES birds(ring_num);

    ALTER TABLE race_birds ADD CONSTRAINT fk_races_parent FOREIGN KEY (raceid)
    REFERENCES races(raceid);

    SELECT o.first_name, b.ring_num, b.descr bird_descr,
    r.raceid, race_date --etc
    FROM owners o
    inner join birds b on o.ownerid=b.ownerid
    inner join race_birds rb on b.ring_num=rb.ring_num
    inner join races r on rb.raceid=rb.raceid
    ;

  9. #9
    Join Date
    Oct 2005
    Posts
    15
    Is It something Like this? Sorry


    Owner Table Bird Table Race Table
    OwnerID Key OwnerID (foreign Key) BirdID (Foreign Key)
    Name BirdId (Key) Date
    Club Race Name
    Area Results
    RACEID (Key)

  10. #10
    Join Date
    Oct 2005
    Posts
    15
    Sorry didnt turn out will try and get an image. I am using mysql> Testing on xampp

  11. #11
    Join Date
    Oct 2005
    Posts
    15
    Something Like this

    Click image for larger version. 

Name:	Capture.JPG 
Views:	0 
Size:	24.5 KB 
ID:	17418

    Sorry for the bother
    Attached Thumbnails Attached Thumbnails Capture.PNG  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •