Results 1 to 8 of 8
  1. #1
    Join Date
    May 2008
    Posts
    4

    Unanswered: Mmembership Database

    Hello there, im new to databases so pelase bear with me :d

    Im tryign to create a database with a relationship to be used for a make believe sports club, the problems i am having is that every tiem i make a rleationship with another table and save it, when i come back it has gone, also, in the relationship view i seem to have a lot of tabels appear when i try and show a table in the relationships view

    I hope this makes sence and i hope someone can help, as i have attached my database with this post

    Regards
    Attached Files Attached Files

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Are you clicking the Save button in the relational diagram screen when you're done?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    May 2008
    Posts
    4
    heh, yes i am, i realise my database may look like i havent a clue! but i realise how to save (i think )

  4. #4
    Join Date
    May 2008
    Posts
    4
    anyone else have any ideas?

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    In the Relationship Window, hit the Show All Relationships button. When the total mess appears, delete all of your lines. Not the tables, the lines. Click each one and Delete. When you have finished that, remove all the tables and save again.

    Now that's sorted, you can then add your tables and then relate them in the normal way... which you can't do until you get your fields right. Autonumber keys need Numeric foreign keys.

    Cheers

    ST
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    thats just the way the relationships editor displays data. it cannot handle multiple relationships between the same tables using only one display of each table. hence you have got 11 relationships defined between Facilities and members details, 10 of which seem to be repeats of Facilities to members details in the members ID

    however I do think you have some serious problems with your design, and or possibly your naming of tables

    there should never be a need to store both an ID and a name as a realtionship between two tables, where the values in both tabels is the same... either use the ID (preferable) or the name but not both.

    I think you need to relook at your design haivng boned up on normaisations... I usually point people at Rudy's (R937) site. it has an excellent piece by Paul Litwin.. one of the authors of the Excellent Access Developer books. Pootle Flump reccomends another good reference by Tony Marsden.. but I can't place the URL right now

    Id suggest you need to rethink what you are modelling

    A facility exists without reference to a member, and vice versa. there is a relationship when a member books to use that facility. a booking is dependent on both the facility and a member, or members. a booking must be unique (you can't have multiple bookings for the same facility at the same time, nor should you be able to allow a member to book more than one facility at any one time). Some leisure clubs run on the basis of slots (ie they prepopulate the bookings tabel with slots available (say 30 minute, 45 minute, 60 minute intervals) and then fill in the bookings as they come in. this isn't the smartest design, but can be the easier to implement for a beginner). it does mean that reports are a doddle to run off, easy to understand to the human

    looking at the members table
    Im guessing the footballuses,gymuses, saunauses are a mechanism for identifying weaht type of membership the member has... bu thats not a very good way of doing it.. what happnes if the club decides to change its facilities eg commit social suicide and add golf to its activities.. your design requires a complete makeover, where as if you defined the types of membership elsewhere, you could then associate a member with each applicable membership.


    BTW when defining relationships Id strongly recommend that you define your relationships as RI relationships.. relationships that enforce data integrity. Id suggest that all relationships are set for cascade updates, and you only use delete cascade once you know what it means, and what it does if you sue delete cascade (i think Delete cascade is a reasonable choice for details but NOT for masterfiles (eg types of membership, facilities and so on.. its fine for members (providing you don't care about the history of an ex members bookings .. its fine to use delete cascade on member)
    HTH
    Last edited by healdem; 05-14-08 at 06:54.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    May 2008
    Posts
    4
    Quote Originally Posted by healdem
    thats just the way the relationships editor displays data. it cannot handle multiple relationships between the same tables using only one display of each table. hence you have got 11 relationships defined between Facilities and members details, 10 of which seem to be repeats of Facilities to members details in the members ID

    however I do think you have some serious problems with your design, and or possibly your naming of tables

    there should never be a need to store both an ID and a name as a realtionship between two tables, where the values in both tabels is the same... either use the ID (preferable) or the name but not both.

    I think you need to relook at your design haivng boned up on normaisations... I usually point people at Rudy's (R937) site. it has an excellent piece by Paul Litwin.. one of the authors of the Excellent Access Developer books. Pootle Flump reccomends another good reference by Tony Marsden.. but I can't place the URL right now

    Id suggest you need to rethink what you are modelling

    A facility exists without reference to a member, and vice versa. there is a relationship when a member books to use that facility. a booking is dependent on both the facility and a member, or members. a booking must be unique (you can't have multiple bookings for the same facility at the same time, nor should you be able to allow a member to book more than one facility at any one time). Some leisure clubs run on the basis of slots (ie they prepopulate the bookings tabel with slots available (say 30 minute, 45 minute, 60 minute intervals) and then fill in the bookings as they come in. this isn't the smartest design, but can be the easier to implement for a beginner). it does mean that reports are a doddle to run off, easy to understand to the human

    looking at the members table
    Im guessing the footballuses,gymuses, saunauses are a mechanism for identifying weaht type of membership the member has... bu thats not a very good way of doing it.. what happnes if the club decides to change its facilities eg commit social suicide and add golf to its activities.. your design requires a complete makeover, where as if you defined the types of membership elsewhere, you could then associate a member with each applicable membership.


    BTW when defining relationships Id strongly recommend that you define your relationships as RI relationships.. relationships that enforce data integrity. Id suggest that all relationships are set for cascade updates, and you only use delete cascade once you know what it means, and what it does if you sue delete cascade (i think Delete cascade is a reasonable choice for details but NOT for masterfiles (eg types of membership, facilities and so on.. its fine for members (providing you don't care about the history of an ex members bookings .. its fine to use delete cascade on member)
    HTH
    Hi, thank you all for your replys so swiftly, i dont mean to dis regard the above post as you have put so much effort in, but as i am a beginner will the databasse that i have just created do as a starting point, i will be going to university next eyar and there is a compulsary database unit in that, at which point i can use all the extras then? as it is, following the post about deleting the relationships and re making them, will that do?

    regards

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Depends on what you want to achieve I guess. Doing what I said will fix your immediate issue, but that takes nothing away from healdem's points.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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