Page 1 of 4 123 ... LastLast
Results 1 to 15 of 49
  1. #1
    Join Date
    Dec 2006
    Posts
    21

    Unanswered: help a novice create a swimming club database

    well as i said im a novice and just started to learn about databases.

    ive read about normalisation and understand it a little bit but i cant understand how to apply it to my problem which is to create a swimming club database consisting of members, their training and competitions they enter.

    only a basic membership would be required where there are two categories of member, individual and family. the families are made up of a number of names individuals who all reside at the same address and receive a single new letter and other correspondence. they pay a single family subscription.

    i would also need to enter members date of birth, sex, phone number, passport number and summer of health records.

    the last thing i would want is payment records of subscription, although i dont know what fields to have for this. i would guess and say we need to know if they have paid, when they did and when the subscription expires and needs renewing.

    this information is only for membership and i think its best to start here.

    any help would be so welcome.

  2. #2
    Join Date
    Jan 2006
    Posts
    8

    what do you have built so far and what are you stuck on?

    more detail be helpful

  3. #3
    Join Date
    Dec 2006
    Posts
    21
    what other details would you like to know? i have been working on this today and have come up with the following tables so far -

    membership - (membershipID, surname, phoneNumber, Address1, Address2, Address3, NumberOfMembers)

    members - (Forename, Surname, DateOfBirth, Sex, PassportNumber) [i cant think what could be primary key here. i think i could use passportNumber but i dont know if its appropriate]

    subscription_cost (NumberOfMembers, price)

    health_records (height, weight, lung capacity, dateChecked) [dont know about a primary key]

  4. #4
    Join Date
    Nov 2003
    Location
    Sussex, England
    Posts
    404
    I hate to say it but I think your design is not on the right lines.

    Membership and Members should be one table: Member. The Primaty key should be an autonumber field (ID or similar).

    Subscription is basically a reference table (I would imagine) with a couple of fields: Type; Cost (with another autogenerated primary key).

    To be honest you need to go back to the drawing board on design.

    What information do you need to hold? Where does it come from? What needs to be displayed? What reports do you need? These questions are needed as their answers will impact on the design. You certainly shouldn't have the surnames of members in more than one table.

    If numberOfMembers is a total of the members then it does not belong in a table, it should be generated for a report by the front end of your database. If its the members IF number it would be a good idea to refer to it with fk (Foreign key in the name) e.g. fk_memberID.

    Payment and Health would probably be separate tables with foreign keys linking to the member table.

    I think you should draw the schema for the database and post that here if you want further help with this project.

  5. #5
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Okay I'm not sure what your trying to do, but this is my best guess as for how you need to change the tables described

    subscription (SubscriptionID,subscriptionType , Surname, PhoneNumber, Address1, Address2, Address3)
    member - (MemberID,SubscriptionID, Forename, Surname, DateOfBirth, Sex, PassportNumber)
    health_records (MemberID, DateChecked,Height, Weight, LungCapacity) this is a composite primary key
    subscriptionType (Size, Price)
    Last edited by m.timoney; 12-21-06 at 05:47.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  6. #6
    Join Date
    Dec 2006
    Posts
    21
    when you mean drawing a schema for the database, do you mean like a relational diagram? something like below?

    http://www.wutka.com/hackingjava/f15-4.gif


    basically the main thing i dont understand is how to hold data of both types of member category, individual and family.

    i think i should type up all my requirements.

    membership - a basic membership record is required

    • there are two categories of member, individual and family
    • families are made up of a number of named individuals who all reside at the same address and receive a single new letter and other correspondence
    • they pay single family subscription
    • i need to store the members date of birth, sex, phone number, passport number and summery of health records
    • store payment records of subcriptions



    training - coaches carry out regular monitoring of the swimmers

    • record height, weight, lung capacity at specific dates
    • attendance at training sessions
    • timed distances for specific strokes on recorded dates e.g.59 secs for 100m front swim on 16/06/06
    • daily training programmes for each individual swimmer so any coach call up what any swimmer is meant to be doing at each training session



    competition - coaches want a complete record of every competition entered by swimmers so they can recall who beat whom and what time rivals in individual races have achieved in the past and what the swimmers must do to win

    • date, location of time of swimming gala
    • the results of each event including the name and position of each swimer and their time

  7. #7
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    okay thats about what i though you where trying to do. I renamed the membership table to clearly mark the difference between it and member but thats purely cosmetic

    competitions (competitionID,date, time, Location)
    events(EventID)
    results(competitionID, EventID, MemberID,position,time)

    PS becareful about what information you keep you could easily find youself in breach of the local Data Protection Laws
    Last edited by m.timoney; 12-21-06 at 08:38.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  8. #8
    Join Date
    Dec 2006
    Posts
    21
    m.timoney thanks for your help and advice.

    your tables seem to work about right

    do you think i should add another field in events where the type of event is associated with a EventID such as 100 meter butterfly.

    e.g

    event id eventType
    1 100meter butterfly

    also how would i link the competition tables to membership? is it via the results table linking with the member table?

    and what kinda of tables would i make for the training section of my requirments.

    it looks nearly finished which is good.

  9. #9
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    You could do, or you could have "100m Butterfly" as the ID. it doesn't make a huge difference either way (Tiny bit more hard drive per record)

    also how would i link the competition tables to membership? is it via the results table linking with the member table?
    yes, default the times and position to 0 for a event
    Last edited by m.timoney; 12-21-06 at 12:50.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  10. #10
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    record height, weight, lung capacity at specific dates
    --I've handled this one, you just add the coachId if you want to reference the coach

    attendance at training sessions
    --simalar to the competitions table but with attendence added

    timed distances for specific strokes on recorded dates e.g.59 secs for 100m front swim on 16/06/06
    --simalar to the results table only with the competitionID removed and no position, which reminds me the position can be calcuated if you don't want it in the results table

    daily training programmes for each individual swimmer so any coach call up what any swimmer is meant to be doing at each training session
    --this depends on the way you want to do it. the easiest would be to add a target to the previous answer
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  11. #11
    Join Date
    Dec 2006
    Posts
    21
    im struggling with this as im trying not to duplicate data but it seems pretty difficult.

    at the moment for training ive got -

    health_records(memberID,dateChecked, height, weight, lungCapacity)
    training_session(date, time, location)
    attendance - dont know how to make this without duplication of data
    timed_distances((date,(memberID,(eventID,time)

    training program - i would want this table to created and linked in a way that i would be able to query such as, "what is the training requirement for carls berg on 12/03/06.

  12. #12
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    for Attendance try this: training_session(date, time, location, attended)

    i'm not quite sure what you want in the training program, and tomorrow i'm off on my criggy hol's until the 29th so get if your not quick you might have to wait until after xmas
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  13. #13
    Join Date
    Dec 2006
    Posts
    21
    but what would i put in the attended field?

    i think the training program table can somehow be linked to the training session table.

    what i need in the training program is probably event type, the member and the date of the training session.

    i basically want to be able to query such as what each member has to do in training.

  14. #14
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    a boolean, thats yes/no in access i think

    okay i think you've got the right idea there.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think that you are on the right lines.. but not 'there' yet with your design

    In my view a good approach is to think of the entities (discrete pools [pardon the pun] of information.

    so Id expect to see somehting like:-
    an entitiy to store:...

    details of members
    (name, address, cellphone, email..... whatever), arguably if the member has nore than one address, or cellphone then you could have a sub table hadling that (eg a work address, a home address, a local address, a work cellphone, a home cellphone etc...) However Im guessing thats not really required for this. Id expect to see a membership number... almost certainly a system generated / autonumber. Incidentally I wouldnt use a passport number od SSN or whatever.. somepeople may not have a Passport, some people passports may not be compatible with your system (you dont know what paasports may exists out there in the real world).. somepeople may not think your request is relevant... why would you need toknow soemones passport or SSN in a swimming club?). But the realll knockout in my nmnd on paasports is how do you handle when a member shcnages their passport (eithger the old one expires or they apply for their first passport or they accquire dual /multiple nationality).

    details of types of membership
    often a code, a description and a fee payable. dependign on the club a member may have more than one fee payable (there may be a joining fee, specialist subscription fee (eg if there was a specialist training option, a general membership fee). Some itmes you may have a code to indicate that no fee is apyable (eg they are honorary members, or there fees are paid by a third party. If the latter is thge cas then you need to think as to how the fees are paid (are fees paid ONLY by another member or are they paid by non members... if so then how do you store that information.

    details of fees paid/not paid
    presumably the club needs to know who has paid, who to chase, who to chuck out for non payment. you may need to refine that to allow for any auditing or referential integrity checking to ensure no one is tinkering with the fees file.

    details of the events or courses run
    (so you probably need to indentify any differneces between training events and competition
    ...and who attended those courses (thats a linking or refernnceing table where you associate a specific event with specific members in a table that references BOTH members and events

    details of what specific training programmes are... are these training programs unique to an individual or are they generic.... IE all men over 'n' age must complete 'x' lengths backstroke, 'y' lengths breaststroke etc.... so you probably need an entity to identify the types of swimming, or arguably activity (yes you could use Access 'helpfull' lookup wizard (as their are only 4 types of wimming Im aware of... please resist the temptation it will bite you in the backside if you do use it). It may be that your event type should identify if its a swimming (eg Butterfly) or non swimming activity (eg running, weights, circuit training... whatever)

    it does help if you skecth out your entities, and it does help if yopu fill in partial details of what the entities are, awhat they contain what they represent.

    some of the events may require a time recording or a number (eg in a swimming meeting yoiu may be concerned that Fred X did 200 metres Backstroke in n minutes & y seconds, whereas in trainign what may matter is that Fred did n lengths of Butterfly, did x prressups, 'y' minutes on a cardio workout. incidentally you may need to record those stats (eg pulse, recovery time and so on.

    best thing to do.. start small concern yourself with the fundamentals... cover the basic user requirement.. if its a cluib they are probably concerne3d about the who is the membership and have they paid questions. bear in mind that sending out a mailiong list is hard work... its one of the areas which can give the biggest bang for the buck. monitoring who has paid is also important. Having got a firm grasp of databse design principles then, and only then would I start concerning myself about details of events, trainign etc... they are going to be messy.

    I'd resist for as long as possible the temptation to open up Access and start designing tables.. yes its sexy, yes it looks like you are doing soemthing, but ultimately when you first start off there are a heck of a lot of issues that you need to think about. THe fundamental is trying to identiofy the right pool to store the data in. if you start sdesigning tables.. then forms.. the reports then bad design can become so well established that is painfuill to remove it.

    I find it helps to consider only what is relevant to that particaular entity. consider an address. some applications require a single address, or perhaps can define all types off address from day one.... I suspect that in your case that is the case (your membership records require an address (probably their home address, but amybe also a work address)). if you decide to support a family membership how do you store the address (do you store the address at the nominated feepayers address or do you store it also at each members record. if you store it as a spearate item (effectively you are saying each member MUST have a home address, and assocaite the address with as many feepayers as required) but its only updated once. In that address they may have a phone number... is the phone number an element of the member or the address. Well if its a lnadline then its probably an element of the address, a cellphone is probably an element of the membership record.

    one thing to do is to prepare youself for a few blind allies, you are going to do womething that isn't right.. accept that as part of the fee for joining the db design world.. we all do it....
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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