How do I store a product with no unique property

[AnthonySterling]

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!

[FatBat]

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.

[r937]

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 ...

local_id                country  product_id
12345-12345-12345-12345    UK        1
67890-67890-67890-67890    IE        1
ABCDE-ABCDE-ABCDE-ABCDE    DE        1

helps?

[AnthonySterling]

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!

[r937]

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

[AnthonySterling]

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?

[r937]

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?

[AnthonySterling]

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!

[guido2004]

I should use this description shouldn't I?

Use it for what? Are you sure you understood what Rudy is trying to explain?

[AnthonySterling]

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.

[r937]

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?

[AnthonySterling]

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.

[r937]

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?

[IBazz]

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

[r937]

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?

[IBazz]

yup rudy, I makey mistakey.

I was pondering as I typed and messed up. the table should I think just be 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
)

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

[r937]

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