SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    How do I store a product with no unique property

    Hi all,

    I've been tasked to build a database to store some of our products, thing is, as we sell these products under many guises they can be named many things.

    Could any point to a satisfactory solution?

    To add some context, we have 54 sites, all refer to a product using their own GUID. As potentially, all of these sites will be using the database, I'd prefer not use a local GUID for each product.

    Initially, I thought of doing...


    • id
    • site
    • value

    So, this would go...

    1 | UK | 12345-12345-12345-12345
    1 | IE | 67890-67890-67890-67890
    1 | DE | ABCDE-ABCDE-ABCDE-ABCDE-ABCDE

    Seems messy.

    Cheers!
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  2. #2
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    Yorkshire, UK
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would have a table containing your products, with one entry for each product - give each product a unique ID;

    ID - Item
    1 - Blue Handbag
    2 - Red Shoes
    3 - Apple II computer

    Then have another table showing the GUID for each product in each country, eg

    Country - GUID - Product ID
    UK - ABCDE - 1
    IE - 111111 - 1
    FRA - qwerty1 - 2

    This says that the Blue Handbag will be known as ABCDE in the UK and as 11111 in Ireland and the Red Shoes will be known as qwerty1 in France.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    not sure i understand the question

    obviously a product is a product so it should exist in the product table only once

    however, if there are many different identifiers for the same product, you're going to want an additional table to identify which product each refers to

    probably something like this ...
    Code:
    local_id                country  product_id
    12345-12345-12345-12345    UK        1
    67890-67890-67890-67890    IE        1
    ABCDE-ABCDE-ABCDE-ABCDE    DE        1
    helps?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Cheers Rudy, that's comfortingly similar to what I suggested.

    However, you hint at a second table, what would I hold there? No one place calls the product by common identifier...

    Each site essentially describes the product by its attributes or predefined GUID.

    Thanks Rudy, FatBat!
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    the sample data i gave you (which i stole from post #1) ~is~ the second table

    the first table is your products table where each product has only one row and all the standard product details are stored
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Hmmm, right.

    Well, initially we won't be using any other data other than the GUID. I'm scared to ask as it seems so wrong, but would I create a table with just an id column to expand upon at a later date if needed?
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by AnthonySterling View Post
    Well, initially we won't be using any other data other than the GUID.
    whoa, wait a sec...

    your products don't have names? descriptions? sizes? prices?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    A product can be many colours, prices, lengths names although only one of each.

    For instance, CAT5-GREY-500M-LSOH-BRANDINGCODE...

    So, this collection of attributes identifies the product, so, wait a moment...

    I should use this description shouldn't I?

    Oh the shame.

    Sometimes, you just need to chat with someone...

    Unless I'm wrong, cheers Rudy!
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by AnthonySterling View Post
    I should use this description shouldn't I?
    Use it for what? Are you sure you understood what Rudy is trying to explain?

  10. #10
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    I think so guido2004, although on further thinking, it seems like redundant data.

    • product
      • id
      • description



    • product_code
      • product_id
      • site
      • code


    You see, I wont be using the description at all, I just need to be able to gather all the product codes used by the sites if I provide one code.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by AnthonySterling View Post
    For instance, CAT5-GREY-500M-LSOH-BRANDINGCODE...
    that second part of the code, that sure looks like a colour

    you're saying that this grey product might not be grey in different locations?

    then it's probably not the same product

    i'll bet if you try hard enough, you can find some additional attributes of products that aren't constant

    heck, with this type of model, you can sell battleships and kitty litter under the same product id!!!



    seriously, how many different products do you gots?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    OK.

    We have a few hundred product bases, lets say one is a 'Door'.

    This door can be many colours, sizes, glazed or not, if it is glazed it might have many different glass types and each of these glass types can have many treatments....and so forth.

    However, all sites reference "A brown door with bronze hinges, glazed with Rice Crackers and Cheddar Cheese and then cut in half diagonally." with different codes.

    Thanks for the help, database stuff isn't my forte and I want to get this as correct as possible.

    Thanks again,

    Anthony.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by AnthonySterling View Post
    However, all sites reference "A brown door with bronze hinges, glazed with Rice Crackers and Cheddar Cheese and then cut in half diagonally." with different codes.
    is this the only door you gots?

    if you had almost exactly the same door but with brass hinges instead, wouldn't you consider that a different product? with a different product_id?

    like i said, if you don't distinguish between different products in your model, then you could sell battleships and kitty litter under the same product_id, even though all your sites would have different codes for that product_id

    however, since "door" is only one of "a few hundred product bases" then all doors would have the same product_id even though one's a wooden door and the other's a screen door?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rewinding a bit - to post 1, you say you have a local guid for each product.

    OK, i get that. And it tells me that no matter what, they are all different products. maybe it'll be price or VAT rate but they will be diferent from country to country and those differences will also vary from country to country. (look at how VAT changed in the UK in a different way to any other - as an example).

    You may also find that the product in one country may appear to be the same as in another but, it is made from different quality materials and therefore was cheaper to buy in. it may also have a different selling price because of this and may not be on sale throughout all your stores because various other qualities of product replace them. An example of this would be cars throughout the EU. a Merc in Greece used to be a different spec entirely from one in the UK and it was badged the same. Likely had a different local GUID.

    such localised changes or rather, the ability to accommodate them, may be lost if you 'over globalise' - meaning your db will become unworkable/unusable.

    I would simply add each localised record to a products table like this

    create table products
    ( global_id int not null auto_increment primary key
    , guid varchar(32) not null
    , country char(3) not null
    , other data
    , unqiue index (global_id,local_guid)
    )

    The you can call up any product based ewither on its global_id, local_guid or even its country of sale.

    now I'll hide until after an expert returns.

    bazz

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by IBazz View Post
    create table products
    ( global_id int not null auto_increment primary key
    , guid varchar(32) not null
    , country char(3) not null
    , other data
    , unqiue index (global_id,local_guid)
    )
    if global_id is guaranteed to be unique, then (global_id,local_guid) is also guaranteed to be unique, you don't need to specify it separately

    i'm guessing this isn't what you intended?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yup rudy, I makey mistakey.

    I was pondering as I typed and messed up. the table should I think just be like this

    Code:
    create table products
    ( global_id int not null auto_increment primary key
    , guid varchar(32) not null
    , country char(3) not null
    , other data
    )
    with other data such as constraints added as well.

    Do you agree with this being the simpler/best approach to the OP's question?

    bazz

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i think either

    1) this would require unnecessary repetition of "other data" for all countries

    assuming it's the same for all countries

    if it isn't, then it isn't the same product, is i


    alternatively, your last table layout suggests that

    2) each product can belong to only one country
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •