Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2008
    Posts
    4

    Unanswered: Creating Relationships and Normalizing Tables Problem.

    Alright, I am a first year database student and am just learning access. I have been given the task of creating a database that is as normalized as possible mimicking the structure of a parking lot. There are a list of spots, one car can only park in a spot, a customer can have a list of guests that can park in that spot, the customer may also have a list of many cars that can park in that spot. The cars are listed by make, model, color, year, licence plate, etc. I seem to have a grasp on breaking up the entities for normalization, but am stuck on the customer -> parking spot relationship. It should be a 1-to-1 in my opinion as a customer may only have one spot and a spot can only be rented by one person. I don't understand how to go about this, would I just put the customer ID and Name into the parking Spot table and make the relationships from the parkingSpots table. I just don't think the one-to-many from parking spot to customer looks right. Attached is the picture of my relationships at the moment. Thank you in advance to anyone taking the time to help me out.

    Jeramie.[ATTACH]Click image for larger version. 

Name:	relationships.JPG 
Views:	56 
Size:	30.4 KB 
ID:	8843[/ATTACH]

  2. #2
    Join Date
    Aug 2008
    Posts
    58
    I'm not an expert here, but it seems to me that if a one-to-one relationship is what you want, then "Parking Spots" are functionally dependent on Customers. They don't need a separate table. "Rent date" is also functionally dependent on Customers, since that is when a Customer record would be created. Generally speaking, an existing one-to-one relationship is a good sign that the two things belong in the same table.

    J

    Edit: This is probably a question for the General DB Design forum.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    depends on the needs of the business problem
    a car park has many car park slots
    but the question in my mind is do you need to identify specific parking slots. in modellng terms do you need to track that slots H1..50 are allocated to compay X, or do you merelyt need to record that H1..50 are allocated.... do ypou need to know at the micro level which parking slots are free or do you need to know that capacity is (say) 500 spaces, of which (say) 150 are pre let to companies, 200 are occupied leaving 150 free spaces.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Oct 2008
    Posts
    4
    Thank-you for the replies. I have added the customerName into the parkingSpots table and adjusted relationships accordingly. The basis of the project is to track parking spots on an individual basis, see who rents the spot, see the guests that are allowed to park there, and see what car is in what spot. Now I seem to have run into another snag. Since cars have a one-to-many relationship with a parking spot, (a customers family and friends may use their spot), I don't know how to go about implementing a relationship where only one car can park in its allocated spot at a time. Attached is the updated relationships.

    Thanks for the help, Jer.
    Attached Thumbnails Attached Thumbnails relationships.JPG  

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    to enforce uniqueness you have two choices make those attributes part of the primary key, or make them pat of a unique index (you can make a unique index that isn't part of the PK, by definition a PK is a unique index)

    do you care about who has had what parking space over time?

    what makes 'guests' different to normal spaces?

    do you need to track the make, model of car. or would storing
    VW Golf, Red be enough

    if you need the car data for the regular customers why would you not want it for the guests
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    "do you need to track the make, model of car. or would storing
    VW Golf, Red be enough"

    Use the cars 'Primary Key' - Registration plate!
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

Posting Permissions

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