Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2010
    Posts
    2

    Database normalization

    I'm storing tourist attractions in a database. Each attraction has the following:

    AttractionID
    Name
    Latitude
    Longitude
    Country
    Info
    Picture
    PictureHeight
    PictureWidth

    How could I normalise this database?

    Currently I have a PictureTable, LocationTable, NameTable, and InfoTable. However, the primary key in each of these tables is the AttractionID, and I know this is wrong.

    Help please?

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pistonhead View Post
    How could I normalise this database?
    only the picture width and height are wrong, as they depend on the picture and not on the attraction

    so a separate picture table is warranted, and the primary key would be a unique identifier for each picture, with a foreign key in the attractions table

    your other tables (LocationTable, NameTable, and InfoTable) are not necessary
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2010
    Posts
    2
    Quote Originally Posted by r937 View Post
    only the picture width and height are wrong, as they depend on the picture and not on the attraction

    so a separate picture table is warranted, and the primary key would be a unique identifier for each picture, with a foreign key in the attractions table

    your other tables (LocationTable, NameTable, and InfoTable) are not necessary
    Thankyou.

    Some attractions are in the same country though. Would this mean I need a seperate 'Country' table, with CountryID, which is a foreign key in the AttractionsTable?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pistonhead View Post
    Would this mean I need a seperate 'Country' table, with CountryID, which is a foreign key in the AttractionsTable?
    no it wouldn't

    have you ever seen an employees table, with firstname and lastname columns?

    just because two employees have 'John" as firstname, doesn't mean you automatically need to pull firstname out of the employees table and set up a foreign key to a "firstnames" table

    normalization means does each non-key attribute fully depend on the entire primary key

    so if i give you the value of an attractionid, can you unambiguously pull out the value of the country for that particular attraction? yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not for normalisation.

Posting Permissions

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