SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,411
    Mentioned
    81 Post(s)
    Tagged
    3 Thread(s)

    Table design advice - products in location

    I have a table containing 88 locations - 88 rows and 2 columns = ID & location name
    I have a table containing 11 products - 11 rows and 3 columns = ID, product code & product full name

    Both these tables are used for other queries and as I am only starting on the site I could add more columns if required.

    What I want now is to know how many products are in each location and am not sure of the best way to go about it.

    Not all locations have all the products and I could create a table with a location ID column, products ID column and qty column. This table will still be quite large and I keep thinking there must be a better/more efficent way to do it.

    Any suggestions would be gratefuly recived.

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    If a product can be in multiple locations, and a location can have multiple products, then the table you described is exactly the right way to store that information. There is no more compact way -- there's no duplication of data there, so nothing you could eliminate with another design.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Rubble View Post
    I could create a table with a location ID column, products ID column and qty column. This table will still be quite large and I keep thinking there must be a better/more efficent way to do it.
    that actually is the best way to do it

    at most you are looking at a few hundred rows -- a trivially small table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,411
    Mentioned
    81 Post(s)
    Tagged
    3 Thread(s)
    Thanks for the coments; I will carry on with the method I outlined.


Bookmarks

Posting Permissions

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