Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2008
    Posts
    2

    Unanswered: Normalisation and Relationships Problem

    Hi

    I'm very new to Access databases and I'm trying to set up a system for a DVD rental shop. At the moment I have my tables set up as in the attached screenshot, but I've been told that this is not normalised and won't work properly. Apparently I need to add another table. I'm also not sure what type of relationships I should have.

    Any help would be much appreciated.

    Thanks
    Attached Thumbnails Attached Thumbnails Picture 2.png  

  2. #2
    Join Date
    Feb 2004
    Posts
    214

    ...

    I don't see any inherent problems. Your PR Rental is your main table to store the rentals. The other two tables provide additional details about the dvd and the customer. I would add another calculated field on your PR Rental table for total charges. The Nights Rented and your Total Charges can be updated via queries based on the Nightly Rental Rate and the DateDiff of the Rent Date and the Return Date.

    How do you plan on handling dvd's that a customer loses and has to pay for?
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  3. #3
    Join Date
    Nov 2008
    Posts
    2
    Thanks for the response. Good to know I'm on the right track. Not sure about lost DVDs, have to think about that one

  4. #4
    Join Date
    Feb 2004
    Posts
    214

    ...

    When you are setting up your tables, try to think of every variable and scenario. Then think of what you want to be able to report on.

    Other examples, in the DVD table, do you want to know how many of each DVD you have. Then you can subtract to see how many are check out, and then you can use that query to check inventory.

    You may want to have a DVD fees table, so that when they check out a customer, the they can choose (New Release, Older Title, etc...) and the system will attach the price from your table.

    You may want to add another field to your customer table with additional people that are allowed to rent under that person's name.

    Just some stuff to think about.
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Erm..... I see many issues tbh.

    1. Remove spaces and abnormal characters such as question marks from table names and field names.
    2. Film Genre does not seem to belong to the customer table.
    3. Paid Membership, JoinDate and payment method should be in another table called Membership or Subscriptions. Hard to tell really because we don't know how memberships work in the business you are modelling.
    4. Nights Rented does not need to be stored. It can be calculated each time you want to see it.
    5. You should have a DefaultCostPerNight field in the DVD table. That should be copied to the CostPerNight field in the Rental table when the DVD is selected.
    6. You might want to consider having related tables for Genre, Actor and Rating too, but that kinda comes down to the goal.

    I disagree with Mr Blonde here; I would not have another field for storing a total. Just re-calculate it each time.
    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
    Feb 2004
    Posts
    214

    I agree with StarTrekker

    Some of the fields I had mentioned would be easier to just calculate as opposed to storing.
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

Posting Permissions

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