You are viewing a plain text version of this content. The canonical link for it is here.
Posted to solr-user@lucene.apache.org by Steve Cerny <sj...@gmail.com> on 2011/08/16 01:28:36 UTC
Product data schema question
I'm working on an online eCommerce project and am having difficulties
building the core / index schema. Here is the way we organize our product
information in a normalized database.
A product model has many SKUs (called colorways)
A SKU has many sizes (called variants)
A SKU size has associated inventory (called variant inventory)
When we setup our product core we have the following field information
Doc
* brand
* model name
* SKU
* color name
Sample records are as follows
* Haynes, Undershirt, 1234, white
* Haynes, Undershirt, 1235, grey
* Fruit of the Loom, Undershirt, 1236, white
* Fruit of the Loom, Underwear, 1237, grey
The issue I'm having is I want to add inventory to each size of each SKU for
faceting. Example,
SKU 1234 has sizes small, medium, large. Size small has 5 in stock, size
medium 10, and size large 25.
In a normalized data table I would have a separate table just for inventory
and related it back to the SKU with a foreign key. How do I store size and
inventory information effectively with Solr?
--
Steve
Re: Product data schema question
Posted by Alexander Ramos Jardim <al...@gmail.com>.
Why don't you use fields for each size? You can update our inventory only in
the event of a size becoming avaiable or unavaviable. That would remove a
lot of the load in inventory update.
Anothe way is to treat each sku/inventory pair as a document.
2011/8/16 Jaeger, Jay - DOT <Ja...@dot.wi.gov>
> Not particularly. Just trying to do my part to answer some questions on
> the list.
>
> -----Original Message-----
> From: Steve Cerny [mailto:sjcerny@gmail.com]
> Sent: Tuesday, August 16, 2011 11:49 AM
> To: solr-user@lucene.apache.org
> Subject: Re: Product data schema question
>
> Thanks Jay, if we come to a reasonable solution are you interested in the
> details?
>
> On Tue, Aug 16, 2011 at 11:44 AM, Jaeger, Jay - DOT
> <Ja...@dot.wi.gov>wrote:
>
> > No, I don't think so. A given core can only use one configuration and
> > therefore only one schema, as far as I know, and a schema can only have
> one
> > key.
> >
> > You could use two cores with two configurations (but that presumably
> > wouldn't be much help).
> >
> > Solr is not a DBMS. It is an index.
> >
> > -----Original Message-----
> > From: Steve Cerny [mailto:sjcerny@gmail.com]
> > Sent: Tuesday, August 16, 2011 11:37 AM
> > To: solr-user@lucene.apache.org
> > Subject: Re: Product data schema question
> >
> > Jay, this is great information.
> >
> > I don't know enough about Solr whether this is possible...Can we setup
> two
> > indexes in the same core, one for product_catalog and the other for
> > inventory? Then using a Solr query we could join the indexed content
> > together.
> >
> > In Sql it would look like this
> >
> > select
> > p.brand
> > , p.model_name
> > , p.sku
> > , p.color_name
> > , i.variant_id
> > , i.variant_count
> > from product_catalog p
> > join inventory i on (p.sku = i.sku)
> >
> > On Tue, Aug 16, 2011 at 8:00 AM, Jaeger, Jay - DOT <
> Jay.Jaeger@dot.wi.gov
> > >wrote:
> >
> > > On the surface, you could simply add some more fields to your schema.
> > But
> > > as far as I can tell, you would have to have a separate Solr "document"
> > for
> > > each SKU/size combination, and store the rest of the information
> (brand,
> > > model, color, SKU) redundantly and make the unique key a combination of
> > the
> > > SKU and the size (presumably by having an additional field called
> > sku_size -
> > > as far as I can tell Solr can't make a key up out of multiple fields).
> > >
> > > But, perhaps you shouldn't store that dynamic inventory information in
> > > Solr. Instead store a key that gets you to an inventory database
> > organized
> > > by SKU and size. In such a schema, Size could be added as a
> multi-valued
> > > field to your existing schema, so you would know what sizes existed, so
> > you
> > > could at least facet on that, maybe. That way Solr doesn't have to be
> > > updated every time the inventory changes. Of course, that won't help
> > with
> > > the faceting on variant inventory.
> > >
> > > -----Original Message-----
> > > From: Steve Cerny [mailto:sjcerny@gmail.com]
> > > Sent: Monday, August 15, 2011 6:29 PM
> > > To: solr-user@lucene.apache.org
> > > Subject: Product data schema question
> > >
> > > I'm working on an online eCommerce project and am having difficulties
> > > building the core / index schema. Here is the way we organize our
> > product
> > > information in a normalized database.
> > >
> > > A product model has many SKUs (called colorways)
> > > A SKU has many sizes (called variants)
> > > A SKU size has associated inventory (called variant inventory)
> > >
> > > When we setup our product core we have the following field information
> > >
> > > Doc
> > > * brand
> > > * model name
> > > * SKU
> > > * color name
> > >
> > > Sample records are as follows
> > >
> > > * Haynes, Undershirt, 1234, white
> > > * Haynes, Undershirt, 1235, grey
> > > * Fruit of the Loom, Undershirt, 1236, white
> > > * Fruit of the Loom, Underwear, 1237, grey
> > >
> > > The issue I'm having is I want to add inventory to each size of each
> SKU
> > > for
> > > faceting. Example,
> > >
> > > SKU 1234 has sizes small, medium, large. Size small has 5 in stock,
> size
> > > medium 10, and size large 25.
> > >
> > > In a normalized data table I would have a separate table just for
> > inventory
> > > and related it back to the SKU with a foreign key. How do I store size
> > and
> > > inventory information effectively with Solr?
> > >
> > > --
> > > Steve
> > >
> >
> >
> >
> > --
> > Steve
> >
>
>
>
> --
> Steve Cerny
> sjcerny@gmail.com
> 715-302-0639
>
--
Alexander Ramos Jardim
RE: Product data schema question
Posted by "Jaeger, Jay - DOT" <Ja...@dot.wi.gov>.
Not particularly. Just trying to do my part to answer some questions on the list.
-----Original Message-----
From: Steve Cerny [mailto:sjcerny@gmail.com]
Sent: Tuesday, August 16, 2011 11:49 AM
To: solr-user@lucene.apache.org
Subject: Re: Product data schema question
Thanks Jay, if we come to a reasonable solution are you interested in the
details?
On Tue, Aug 16, 2011 at 11:44 AM, Jaeger, Jay - DOT
<Ja...@dot.wi.gov>wrote:
> No, I don't think so. A given core can only use one configuration and
> therefore only one schema, as far as I know, and a schema can only have one
> key.
>
> You could use two cores with two configurations (but that presumably
> wouldn't be much help).
>
> Solr is not a DBMS. It is an index.
>
> -----Original Message-----
> From: Steve Cerny [mailto:sjcerny@gmail.com]
> Sent: Tuesday, August 16, 2011 11:37 AM
> To: solr-user@lucene.apache.org
> Subject: Re: Product data schema question
>
> Jay, this is great information.
>
> I don't know enough about Solr whether this is possible...Can we setup two
> indexes in the same core, one for product_catalog and the other for
> inventory? Then using a Solr query we could join the indexed content
> together.
>
> In Sql it would look like this
>
> select
> p.brand
> , p.model_name
> , p.sku
> , p.color_name
> , i.variant_id
> , i.variant_count
> from product_catalog p
> join inventory i on (p.sku = i.sku)
>
> On Tue, Aug 16, 2011 at 8:00 AM, Jaeger, Jay - DOT <Jay.Jaeger@dot.wi.gov
> >wrote:
>
> > On the surface, you could simply add some more fields to your schema.
> But
> > as far as I can tell, you would have to have a separate Solr "document"
> for
> > each SKU/size combination, and store the rest of the information (brand,
> > model, color, SKU) redundantly and make the unique key a combination of
> the
> > SKU and the size (presumably by having an additional field called
> sku_size -
> > as far as I can tell Solr can't make a key up out of multiple fields).
> >
> > But, perhaps you shouldn't store that dynamic inventory information in
> > Solr. Instead store a key that gets you to an inventory database
> organized
> > by SKU and size. In such a schema, Size could be added as a multi-valued
> > field to your existing schema, so you would know what sizes existed, so
> you
> > could at least facet on that, maybe. That way Solr doesn't have to be
> > updated every time the inventory changes. Of course, that won't help
> with
> > the faceting on variant inventory.
> >
> > -----Original Message-----
> > From: Steve Cerny [mailto:sjcerny@gmail.com]
> > Sent: Monday, August 15, 2011 6:29 PM
> > To: solr-user@lucene.apache.org
> > Subject: Product data schema question
> >
> > I'm working on an online eCommerce project and am having difficulties
> > building the core / index schema. Here is the way we organize our
> product
> > information in a normalized database.
> >
> > A product model has many SKUs (called colorways)
> > A SKU has many sizes (called variants)
> > A SKU size has associated inventory (called variant inventory)
> >
> > When we setup our product core we have the following field information
> >
> > Doc
> > * brand
> > * model name
> > * SKU
> > * color name
> >
> > Sample records are as follows
> >
> > * Haynes, Undershirt, 1234, white
> > * Haynes, Undershirt, 1235, grey
> > * Fruit of the Loom, Undershirt, 1236, white
> > * Fruit of the Loom, Underwear, 1237, grey
> >
> > The issue I'm having is I want to add inventory to each size of each SKU
> > for
> > faceting. Example,
> >
> > SKU 1234 has sizes small, medium, large. Size small has 5 in stock, size
> > medium 10, and size large 25.
> >
> > In a normalized data table I would have a separate table just for
> inventory
> > and related it back to the SKU with a foreign key. How do I store size
> and
> > inventory information effectively with Solr?
> >
> > --
> > Steve
> >
>
>
>
> --
> Steve
>
--
Steve Cerny
sjcerny@gmail.com
715-302-0639
Re: Product data schema question
Posted by Steve Cerny <sj...@gmail.com>.
Thanks Jay, if we come to a reasonable solution are you interested in the
details?
On Tue, Aug 16, 2011 at 11:44 AM, Jaeger, Jay - DOT
<Ja...@dot.wi.gov>wrote:
> No, I don't think so. A given core can only use one configuration and
> therefore only one schema, as far as I know, and a schema can only have one
> key.
>
> You could use two cores with two configurations (but that presumably
> wouldn't be much help).
>
> Solr is not a DBMS. It is an index.
>
> -----Original Message-----
> From: Steve Cerny [mailto:sjcerny@gmail.com]
> Sent: Tuesday, August 16, 2011 11:37 AM
> To: solr-user@lucene.apache.org
> Subject: Re: Product data schema question
>
> Jay, this is great information.
>
> I don't know enough about Solr whether this is possible...Can we setup two
> indexes in the same core, one for product_catalog and the other for
> inventory? Then using a Solr query we could join the indexed content
> together.
>
> In Sql it would look like this
>
> select
> p.brand
> , p.model_name
> , p.sku
> , p.color_name
> , i.variant_id
> , i.variant_count
> from product_catalog p
> join inventory i on (p.sku = i.sku)
>
> On Tue, Aug 16, 2011 at 8:00 AM, Jaeger, Jay - DOT <Jay.Jaeger@dot.wi.gov
> >wrote:
>
> > On the surface, you could simply add some more fields to your schema.
> But
> > as far as I can tell, you would have to have a separate Solr "document"
> for
> > each SKU/size combination, and store the rest of the information (brand,
> > model, color, SKU) redundantly and make the unique key a combination of
> the
> > SKU and the size (presumably by having an additional field called
> sku_size -
> > as far as I can tell Solr can't make a key up out of multiple fields).
> >
> > But, perhaps you shouldn't store that dynamic inventory information in
> > Solr. Instead store a key that gets you to an inventory database
> organized
> > by SKU and size. In such a schema, Size could be added as a multi-valued
> > field to your existing schema, so you would know what sizes existed, so
> you
> > could at least facet on that, maybe. That way Solr doesn't have to be
> > updated every time the inventory changes. Of course, that won't help
> with
> > the faceting on variant inventory.
> >
> > -----Original Message-----
> > From: Steve Cerny [mailto:sjcerny@gmail.com]
> > Sent: Monday, August 15, 2011 6:29 PM
> > To: solr-user@lucene.apache.org
> > Subject: Product data schema question
> >
> > I'm working on an online eCommerce project and am having difficulties
> > building the core / index schema. Here is the way we organize our
> product
> > information in a normalized database.
> >
> > A product model has many SKUs (called colorways)
> > A SKU has many sizes (called variants)
> > A SKU size has associated inventory (called variant inventory)
> >
> > When we setup our product core we have the following field information
> >
> > Doc
> > * brand
> > * model name
> > * SKU
> > * color name
> >
> > Sample records are as follows
> >
> > * Haynes, Undershirt, 1234, white
> > * Haynes, Undershirt, 1235, grey
> > * Fruit of the Loom, Undershirt, 1236, white
> > * Fruit of the Loom, Underwear, 1237, grey
> >
> > The issue I'm having is I want to add inventory to each size of each SKU
> > for
> > faceting. Example,
> >
> > SKU 1234 has sizes small, medium, large. Size small has 5 in stock, size
> > medium 10, and size large 25.
> >
> > In a normalized data table I would have a separate table just for
> inventory
> > and related it back to the SKU with a foreign key. How do I store size
> and
> > inventory information effectively with Solr?
> >
> > --
> > Steve
> >
>
>
>
> --
> Steve
>
--
Steve Cerny
sjcerny@gmail.com
715-302-0639
RE: Product data schema question
Posted by "Jaeger, Jay - DOT" <Ja...@dot.wi.gov>.
No, I don't think so. A given core can only use one configuration and therefore only one schema, as far as I know, and a schema can only have one key.
You could use two cores with two configurations (but that presumably wouldn't be much help).
Solr is not a DBMS. It is an index.
-----Original Message-----
From: Steve Cerny [mailto:sjcerny@gmail.com]
Sent: Tuesday, August 16, 2011 11:37 AM
To: solr-user@lucene.apache.org
Subject: Re: Product data schema question
Jay, this is great information.
I don't know enough about Solr whether this is possible...Can we setup two
indexes in the same core, one for product_catalog and the other for
inventory? Then using a Solr query we could join the indexed content
together.
In Sql it would look like this
select
p.brand
, p.model_name
, p.sku
, p.color_name
, i.variant_id
, i.variant_count
from product_catalog p
join inventory i on (p.sku = i.sku)
On Tue, Aug 16, 2011 at 8:00 AM, Jaeger, Jay - DOT <Ja...@dot.wi.gov>wrote:
> On the surface, you could simply add some more fields to your schema. But
> as far as I can tell, you would have to have a separate Solr "document" for
> each SKU/size combination, and store the rest of the information (brand,
> model, color, SKU) redundantly and make the unique key a combination of the
> SKU and the size (presumably by having an additional field called sku_size -
> as far as I can tell Solr can't make a key up out of multiple fields).
>
> But, perhaps you shouldn't store that dynamic inventory information in
> Solr. Instead store a key that gets you to an inventory database organized
> by SKU and size. In such a schema, Size could be added as a multi-valued
> field to your existing schema, so you would know what sizes existed, so you
> could at least facet on that, maybe. That way Solr doesn't have to be
> updated every time the inventory changes. Of course, that won't help with
> the faceting on variant inventory.
>
> -----Original Message-----
> From: Steve Cerny [mailto:sjcerny@gmail.com]
> Sent: Monday, August 15, 2011 6:29 PM
> To: solr-user@lucene.apache.org
> Subject: Product data schema question
>
> I'm working on an online eCommerce project and am having difficulties
> building the core / index schema. Here is the way we organize our product
> information in a normalized database.
>
> A product model has many SKUs (called colorways)
> A SKU has many sizes (called variants)
> A SKU size has associated inventory (called variant inventory)
>
> When we setup our product core we have the following field information
>
> Doc
> * brand
> * model name
> * SKU
> * color name
>
> Sample records are as follows
>
> * Haynes, Undershirt, 1234, white
> * Haynes, Undershirt, 1235, grey
> * Fruit of the Loom, Undershirt, 1236, white
> * Fruit of the Loom, Underwear, 1237, grey
>
> The issue I'm having is I want to add inventory to each size of each SKU
> for
> faceting. Example,
>
> SKU 1234 has sizes small, medium, large. Size small has 5 in stock, size
> medium 10, and size large 25.
>
> In a normalized data table I would have a separate table just for inventory
> and related it back to the SKU with a foreign key. How do I store size and
> inventory information effectively with Solr?
>
> --
> Steve
>
--
Steve
Re: Product data schema question
Posted by Steve Cerny <sj...@gmail.com>.
Jay, this is great information.
I don't know enough about Solr whether this is possible...Can we setup two
indexes in the same core, one for product_catalog and the other for
inventory? Then using a Solr query we could join the indexed content
together.
In Sql it would look like this
select
p.brand
, p.model_name
, p.sku
, p.color_name
, i.variant_id
, i.variant_count
from product_catalog p
join inventory i on (p.sku = i.sku)
On Tue, Aug 16, 2011 at 8:00 AM, Jaeger, Jay - DOT <Ja...@dot.wi.gov>wrote:
> On the surface, you could simply add some more fields to your schema. But
> as far as I can tell, you would have to have a separate Solr "document" for
> each SKU/size combination, and store the rest of the information (brand,
> model, color, SKU) redundantly and make the unique key a combination of the
> SKU and the size (presumably by having an additional field called sku_size -
> as far as I can tell Solr can't make a key up out of multiple fields).
>
> But, perhaps you shouldn't store that dynamic inventory information in
> Solr. Instead store a key that gets you to an inventory database organized
> by SKU and size. In such a schema, Size could be added as a multi-valued
> field to your existing schema, so you would know what sizes existed, so you
> could at least facet on that, maybe. That way Solr doesn't have to be
> updated every time the inventory changes. Of course, that won't help with
> the faceting on variant inventory.
>
> -----Original Message-----
> From: Steve Cerny [mailto:sjcerny@gmail.com]
> Sent: Monday, August 15, 2011 6:29 PM
> To: solr-user@lucene.apache.org
> Subject: Product data schema question
>
> I'm working on an online eCommerce project and am having difficulties
> building the core / index schema. Here is the way we organize our product
> information in a normalized database.
>
> A product model has many SKUs (called colorways)
> A SKU has many sizes (called variants)
> A SKU size has associated inventory (called variant inventory)
>
> When we setup our product core we have the following field information
>
> Doc
> * brand
> * model name
> * SKU
> * color name
>
> Sample records are as follows
>
> * Haynes, Undershirt, 1234, white
> * Haynes, Undershirt, 1235, grey
> * Fruit of the Loom, Undershirt, 1236, white
> * Fruit of the Loom, Underwear, 1237, grey
>
> The issue I'm having is I want to add inventory to each size of each SKU
> for
> faceting. Example,
>
> SKU 1234 has sizes small, medium, large. Size small has 5 in stock, size
> medium 10, and size large 25.
>
> In a normalized data table I would have a separate table just for inventory
> and related it back to the SKU with a foreign key. How do I store size and
> inventory information effectively with Solr?
>
> --
> Steve
>
--
Steve
RE: Product data schema question
Posted by "Jaeger, Jay - DOT" <Ja...@dot.wi.gov>.
On the surface, you could simply add some more fields to your schema. But as far as I can tell, you would have to have a separate Solr "document" for each SKU/size combination, and store the rest of the information (brand, model, color, SKU) redundantly and make the unique key a combination of the SKU and the size (presumably by having an additional field called sku_size - as far as I can tell Solr can't make a key up out of multiple fields).
But, perhaps you shouldn't store that dynamic inventory information in Solr. Instead store a key that gets you to an inventory database organized by SKU and size. In such a schema, Size could be added as a multi-valued field to your existing schema, so you would know what sizes existed, so you could at least facet on that, maybe. That way Solr doesn't have to be updated every time the inventory changes. Of course, that won't help with the faceting on variant inventory.
-----Original Message-----
From: Steve Cerny [mailto:sjcerny@gmail.com]
Sent: Monday, August 15, 2011 6:29 PM
To: solr-user@lucene.apache.org
Subject: Product data schema question
I'm working on an online eCommerce project and am having difficulties
building the core / index schema. Here is the way we organize our product
information in a normalized database.
A product model has many SKUs (called colorways)
A SKU has many sizes (called variants)
A SKU size has associated inventory (called variant inventory)
When we setup our product core we have the following field information
Doc
* brand
* model name
* SKU
* color name
Sample records are as follows
* Haynes, Undershirt, 1234, white
* Haynes, Undershirt, 1235, grey
* Fruit of the Loom, Undershirt, 1236, white
* Fruit of the Loom, Underwear, 1237, grey
The issue I'm having is I want to add inventory to each size of each SKU for
faceting. Example,
SKU 1234 has sizes small, medium, large. Size small has 5 in stock, size
medium 10, and size large 25.
In a normalized data table I would have a separate table just for inventory
and related it back to the SKU with a foreign key. How do I store size and
inventory information effectively with Solr?
--
Steve