February 23, 2019 at 12:09 pm
A store sells products. Products have attributes grouped into types. (Ex. Product 'shirt' has size and color, so a sku is unique for a xlarge, red shirt).
I have a structure as follows:
[Product] <1:m> [ ProductAttributeType] <m:1> [AttributeType] <1:m> [Attribute]
Querying across these tables for product 1 (Hard Drive) yields this result:
[ProductName] [AttributeTypeName] [AttributeName]
----------------------------------------------------
'Hard Drive' 'Size' '2TB'
'Hard Drive' 'Size' '4TB'
'Hard Drive' 'Size' '8TB'
'Hard Drive' 'Type' 'Mech'
'Hard Drive' 'Type' 'SSD'
I need to develop a query that creates a result that has one row for every product’s attribute combination (ex. 3^2=6 results):
[Sku] [Product] [Attributes]
---------------------------------------------
1 'Hard Drive' '2TB, Mech'
2 'Hard Drive' '2TB, SSD'
3 'Hard Drive' '4TB, Mech'
4 'Hard Drive' '4TB, SSD'
5 'Hard Drive' '8TB, Mech'
6 'Hard Drive' '8TB, SSD'
7 . . . . . .
I'll use the query to re-generate a sku table's rows when rows in any of the above tables is edited, created or deleted.
I appreciate any guidance on approaching this problem.
February 24, 2019 at 3:44 pm
pfefferl - Saturday, February 23, 2019 12:09 PMA store sells products. Products have attributes grouped into types. (Ex. Product 'shirt' has size and color, so a sku is unique for a xlarge, red shirt).I have a structure as follows:
[Product] <1:m> [ ProductAttributeType] <m:1> [AttributeType] <1:m> [Attribute]Querying across these tables for product 1 (Hard Drive) yields this result:
[ProductName] [AttributeTypeName] [AttributeName]
----------------------------------------------------
'Hard Drive' 'Size' '2TB'
'Hard Drive' 'Size' '4TB'
'Hard Drive' 'Size' '8TB'
'Hard Drive' 'Type' 'Mech'
'Hard Drive' 'Type' 'SSD'I need to develop a query that creates a result that has one row for every product’s attribute combination (ex. 3^2=6 results):
[Sku] [Product] [Attributes]
---------------------------------------------
1 'Hard Drive' '2TB, Mech'
2 'Hard Drive' '2TB, SSD'
3 'Hard Drive' '4TB, Mech'
4 'Hard Drive' '4TB, SSD'
5 'Hard Drive' '8TB, Mech'
6 'Hard Drive' '8TB, SSD'
7 . . . . . .I'll use the query to re-generate a sku table's rows when rows in any of the above tables is edited, created or deleted.
I appreciate any guidance on approaching this problem.
Hard to tell from the description, but this sounds like a classic subclassing problem. All items have a SKU and basic description, but they have totally different attributes. Shirts have (color, size) while bicycles have totally different attributes.
February 24, 2019 at 6:43 pm
pietlinden - Sunday, February 24, 2019 3:44 PMpfefferl - Saturday, February 23, 2019 12:09 PMA store sells products. Products have attributes grouped into types. (Ex. Product 'shirt' has size and color, so a sku is unique for a xlarge, red shirt).I have a structure as follows:
[Product] <1:m> [ ProductAttributeType] <m:1> [AttributeType] <1:m> [Attribute]Querying across these tables for product 1 (Hard Drive) yields this result:
[ProductName] [AttributeTypeName] [AttributeName]
----------------------------------------------------
'Hard Drive' 'Size' '2TB'
'Hard Drive' 'Size' '4TB'
'Hard Drive' 'Size' '8TB'
'Hard Drive' 'Type' 'Mech'
'Hard Drive' 'Type' 'SSD'I need to develop a query that creates a result that has one row for every product’s attribute combination (ex. 3^2=6 results):
[Sku] [Product] [Attributes]
---------------------------------------------
1 'Hard Drive' '2TB, Mech'
2 'Hard Drive' '2TB, SSD'
3 'Hard Drive' '4TB, Mech'
4 'Hard Drive' '4TB, SSD'
5 'Hard Drive' '8TB, Mech'
6 'Hard Drive' '8TB, SSD'
7 . . . . . .I'll use the query to re-generate a sku table's rows when rows in any of the above tables is edited, created or deleted.
I appreciate any guidance on approaching this problem.
Hard to tell from the description, but this sounds like a classic subclassing problem. All items have a SKU and basic description, but they have totally different attributes. Shirts have (color, size) while bicycles have totally different attributes.
Yeah I can't figure out a way to describe it simpler. Every product has any number of attribute sets (ex. size, color), each of which have any number of attributes (ex S,M,L,X / red, green, blue). I need to cross join all the combinations of attributes across types, per product, and give each a unique sku number:
February 24, 2019 at 7:55 pm
Before I suggest anything, I'd like to be sure I know the problem. There's a really good article on Subclassing by Rebecca Riordan (but she's implementing it in Access - but that shouldn't matter, the idea is the same).
http://www.theaccessweb.com/tables/tbl0013.htm
Here's the part that's pertinent:
When you’re creating a data model, you often have to deal with differing typestypes of the same basic entity. For example, Customers might be companies or individuals, and you need to store different information for each. Products often have different types, as well. You probably don’t need to store the same information about a book as you do about a piece of software. Books might have the attributes Title, one or more Authors, a Publisher, a Publication Date, a List Price, and a Selling Price. Software, on the other hand, has a Version Number, but no Authors, and you may not care about the Publication Date.
One solution to this problem is to include all of the attributes of all the types in a single table. At first glance, this seems a simple, straightforward solution. But it breaks down quickly as the system adds additional types, with additional fields. Also, the user interface can get ugly very quickly.
A better solution is to borrow a technique from object-oriented design, and sub-class the entities. In relational terms, you set up multiple tables: a master table, which contains the information that is common to all entities, and subsidiary tables that contain the specific information for each type.
In a nutshell, you have a "master" table of items with the columns common to all of them. (Maybe SKU, Name, Price, ItemType) and then "disjoin" tables where you store information about the different properties of each kind of ItemType.
But if that's not your problem then I'm pointing you in the wrong direction.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply