Access Database Design and Query Help (1 Viewer)

[dylanpedro]

Hi,

I am in the process of designing a database but require some help with how the tables should be setup and if queries can return the required information.

This is the basic information of what is required:

The company has many products and each product can either be a single product or belong to a group of products for example all products in the group are the same but the only difference is either different colours or different sizes etc.

On top of this each product can have many different attributes assigned to them for example: finish_type, material_composition etc.

Originally each of these values was set to each product and there was a lot of duplicate values across variation products and therefore it has been advised that these attributes be applied to the group of products rather than each product but if any attribute is different for each product, then that be applied to each product e.g. size, colour etc.

Then when a query is ran it will fetch all the attributes for single product, all the attributes for all grouped products from the variation group and lastly any attributes applied separately to the variation products.

The tables originally we setup as follows:

ProductVariationGroups Table
PK_ProductVariationGroups_ID
VariationGroupName

ProductInfo Table
PK_ProductInfo_ID
FK_ProductVariationGroups_ID REFERENCES ProductVariationGroups (PK_ProductVariationGroups_ID)
Product SKU

Attributes Table (Look up table for values for Single Products or Variation Groups)
PK_Attribute_ID
AttribName
AttribValue
AttribType ?(maybe required, read about later)

ProductAttributes Table (Contains attributes mapped to Individual Products)
PK_ProductAttributes_ID
FK_ProductInfo_ID REFERENCES ProductInfo (PK_ProductInfo_ID)
FK_Attribute_ID REFERENCES Attributes (PK_Attribute_ID)
FK_AttribType REFERENCES Attributes (AttribType) ?(maybe required, read about later)

ProductVariationAttributes Table (Contains attributes mapped to VariationGroups)
FK_ProductVariation_ID REFERENCES ProductVariationGroups (PK_ProductVariationGroups_ID)
FK_Attribute_ID REFERENCES Attributes (PK_Attribute_ID)
FK_AttribType REFERENCES Attributes (AttribType) ?(maybe required, read about later)

First is it possible to have just one table that can store both attributes for individual products and variation groups instead of having two different tables (ProductAttributes/ProductVariationAttributes)

Also using the layout above I have managed to make a union query which shows all the attributes that apply to the Variation group and then any that have been applied to the products themselves e.g.

SELECT ProductInfo.SKU, Attributes.AttribName, Attributes.AttribValue
FROM ProductInfo INNER JOIN (Attributes INNER JOIN ProductAttributes ON Attributes.PK_Attribute_ID = ProductAttributes.FK_Attribute_ID) ON ProductInfo.PK_ProductInfo_ID = ProductAttributes.FK_ProductInfo_ID;

UNION

SELECT ProductInfo.SKU, Attributes.AttribName, Attributes.AttribValue
FROM (ProductVariationGroups INNER JOIN ProductInfo ON ProductVariationGroups.PK_ProductVariationGroups_ID = ProductInfo.FK_ProductVariationGroups_ID) INNER JOIN (Attributes INNER JOIN ProductVariationAttributes ON Attributes.PK_Attribute_ID = ProductVariationAttributes.FK_Attribute_ID) ON ProductVariationGroups.PK_ProductVariationGroups_ID = ProductVariationAttributes.FK_ProductVariation_ID;

The problem with the query is that if an attribute has been applied to the variation group such as AttribNameame = "Gift Wrap Available" AttribValue = "Yes" and then an attribute has been applied to the product such as AttribNameame = "Gift Wrap Available" AttribValue = "No", the query shows both attributes where as the product attribute should take precedence, and therefore the query should only show AttribNameame = "Gift Wrap Available" AttribValue = "No".

I think it has something to do with the Attributes,ProductAttributes and ProductVariationAttributes all needing a column for AttribType and that way the query can tell that both values belong to the same type and only use the value from the ProductAttributes instead of ProductVariationAttributes.

I hope this make sense and have written this in very broad terms to get the basic logic across and am willing to expand on it if necessary. Also I have included screenshots of the basic table relationships and queries used and a sample database.

Regards,
Dylan

 

[dylanpedro]

Also forgot to mention sample data includes all the different types of products that can exist (3 groups with 7 products in total, 1 single product)

Everything is fine in the Merged Query until you get to 00004B001XAM456. As you can see it has two material values as the value "Fabric" has been applied to the group but the value "Faux Leather" has been applied to the product. In the query the value provide for the product should be shown and not the value shown for the group for this particular product?

 

[GinaWhipp]

Can't tell anything from this... what is the Product and what is it you are trying to do? Track inventory? Invoice products? Build something?

 

[plog]

Your relationship view is incorrect. Relationships shouldn't be a loop, there should be only one way to get from one table to another. In your model there are 2 ways between each table (clockwise & counterclockwise).

I don't fully understand your data, but I do know that you need to figure out where Attributes go. It can't be attached to two tables in the manner that it is now.

 

[The_Doc_Man]

To amplify plog's comment - when you have properly identified the elementary entities being tracked, there is no question as to where the attributes go.

A couple of rules need to be considered, and these are PRAGMATIC rather than ABSTRACT THEORETICAL rules.

1. Access won't tell you anything you didn't tell it first. Therefore, don't ask if your queries can return something. Ask yourself this question instead: I need to return X, Y, and Z. Do I capture the data required to find X, Y, and Z. If you frame the question this way, you KNOW whether the query will work.

2. If you can't do it on paper, you can't do it in Access. That is, you have to have some sort of blueprint for how things relate to each other before you implement anything. Otherwise you end up with overspecifying (or underspecifying) relationships. You can't build the relationships or the blueprint until you know in some detail what you wanted to do.

 

[dylanpedro]

Hi,

Thank you for the input everyone.

Lets simplify it as the database is actually for inventory, but what i am trying to do is have an inherit structure to it so...

There is a parent product that can have many child products. Attributes/Product Specifics can be attached to the parent product and all child products will inherit all these attributes/specifics. Attributes/Specifics can also be attached to child products that are only relevant to them.

Can this be modeled so far up to this stage using one table that contains all the attributes (some sort of lookup table that holds static values) but all these values are available to both the parent and child, hence the loop?

 

[GinaWhipp]

Hmm, look up tables can be connected to many tables but should never present in a loop. If they do something is wrong. Perhaps looking at other Data Models will help you understand...

Sample data models...
http://www.databasedev.co.uk/table-of-contents.html
http://www.databaseanswers.org/data_models/
http://www.access-diva.com/tips.html (scroll down to Data Models)

 

[dylanpedro]

Ok, lets start again with just the basic relationships.

Please see the new attachment.

A VariationGroup can have many Products and a Product can belong to many VariationGroup so you end up with a many-to-many table called VariationGroupsToProducts.

Now what if we want each variation group to also be product itself in the Products table, can this be done by simply having a one-to-one relationship between the VariationGroup PK and a FK field in the Products table that can be null?

Kind Regards,
Dylan

 

[plog]

Again, that creates a loop. If there is a many to many relationship between VariationGroups and Products, you need to go through a junction table. Which is what you have done using VariationGroupsToProducts. It also means you shouldn't have a direct relationship between VariationGroups and Products.

Products shouldn't have FK_VariationGroup_ID. When that relationship needs to be made it shoudl go through VariationGroupsToProducts. Get rid of the loop.

 

[dylanpedro]

@plog I've removed the direct link between VariationGroups and Products, the thing is each VariationGroup must have its own parent Product that exists in the Products table and has its own SKU, I have attached the access database with the sample data.

How do I run a query that returns the following:

VariationGroupSKU SKU VariationGroupName
123ParentSKU 123ChildSKU1 123ParentGroup
123ParentSKU 123ChildSKU2 123ParentGroup
456ParentSKU 456ChildSKU1 456ParentGroup
456ParentSKU 456ChildSKU2 456ParentGroup


Does there need to be some kind of flag column in the Products table to indicate each SKU that is a Parent SKU's or something as I really don't understand how else to do what is required?

Kind Regards,
Dylan

 

[plog]

First, I don't know what we are trying to accomplish with the output you've asked for. That being said, this SQL will produce your results:

SELECT Products_1.SKU, Products.SKU, VariationGroups.VariationGroupName
FROM (Products AS Products_1 INNER JOIN VariationGroupsToProducts AS VariationGroupsToProducts_1 ON Products_1.PK_Product_ID = VariationGroupsToProducts_1.FK_Product_ID) INNER JOIN ((VariationGroups INNER JOIN VariationGroupsToProducts ON VariationGroups.PK_VariationGroup_ID = VariationGroupsToProducts.FK_VariationGroup_ID) INNER JOIN Products ON VariationGroupsToProducts.FK_Product_ID = Products.PK_Product_ID) ON VariationGroupsToProducts_1.FK_VariationGroup_ID = VariationGroupsToProducts.FK_VariationGroup_ID
WHERE (((Products_1.SKU) Like "*Parent*") AND ((Products.SKU) Not Like "*Parent*"));

Again, though, what does that do for you? When you do introduce Parent product concept, yes you probably should designate them somewhere.

Can you explain what you are trying to do in English? Use no database terms, just explain to me what this data represents.

 

[jdraw]

Dylan,

As you can see, readers are not following your requirement well enough to give focused answers. We are still trying to understand WHAT you are trying to accomplish. plog has provided SQL to meet your latest question, but we still don't know where that might fit.

Can you tell us in plain English exactly what you are trying to do - try to remove any jargon?
How do you do whatever it is now? Can you describe the processes and the product(s) and what is required to manage things currently?

 

[dylanpedro]

We are trying to make a new database from the ground up as in the past we have been using excel sheets to accomplish what we wanted but we have outgrown these and data is spread over too many sheets and causing too many calculations when ever a value is changed on even one product on the sheet.

We use an off-the-shelf piece of software called Linnworks that allows us to import data via CSV and it has its own rules. We therefore are trying to design our database around their concepts so that the correct information will exist in our database to allow us to produce valid CSV exports.

The first import is called a VariationGroup import and in this import you have a VariationSKU and a VariationGroupName

Once this has been done, product can be mapped to this VariationSKU with another CSV import that has the following data:
SKU and VariationSKU

This concept allows multiple SKU's to be mapped to multiple VariationSKU's and vice versa. I have checked the data held in the product table of this software using a Select * statement and can tell both VariationSKU's which are parents, and SKU's which can be mapped to parents are both present in the same table.

Once we have a VariationSKU or a SKU, you can attach attributes to them such as Color,Size, Category ID and so on. In order to be able to use one table that allows this, both the VariationSKU and SKU must be in the same table and then I intend to have another table that will hold the attribute values in a 1 to many relationship between the products table and a new attributes table. I have checked the existing attributes table in the software and again one table contain attributes that reference both VariationSKU's and SKU's which proves both these exist in the same Products table for this to work.

The main problem right now is as the VariationSKU which is a parent product, also resides in the same table as normal SKU's which can be either child products or single products that have no variations, there is no way of running a select query that only shows child's and singles from the Products table, and ignores the VariationSKU's without some sort of flag column or a different relationship model.

I hope this makes sense and am sorry I ended up explaining in database terms towards the end.

I have included a link to a couple of video showing this in action in the attached txt file as I can not post links in this post.

Kind Regards,
Dylan

 

[dylanpedro]

The company who makes the software has replied with how they store and call the relationships:

New Table for Variation Group ~ [VariationItem]
New Table for Variation Child SKU's ~ [StockItem_Variations]

They have included some sample SQL code which I have attached so I am guessing it is possible to reverse engineer it to work out the table relationships?

I am then able to select the Extended Properties(Attributes/Item Specifics) from another table for both VariationSKU's and Child/Single SKU's using the SQL below:

SELECT
si.ItemNumber , si.ItemTitle
, siep.ProperyName , siep.ProperyValue , siep.ProperyType
FROM
StockItem si
INNER JOIN StockItem_ExtendedProperties siep on si.pkStockItemID = siep.fkStockItemId
ORDER BY
ItemNumber, ProperyName

I hope someone can help decode this so that we can move onto designing the rest of the database tables as there will be quiet a few :banghead:

Kind Regards,
Dylan

 

[plog]

I hope this makes sense and am sorry I ended up explaining in database terms towards the end.

Not quite, your very first sentence explaining it talked about importing CSV files. Sentence two referenced field names. So try again. No database jargon at all. If you ask a mechanic how to get to the closet grocery store, he's not going to talk about cam shafts and tie rods and rear differentials.

Take a step back, think about what this data represents in the real world and talk at that level. No database jargon at all, explain to a 3rd grader what this is to accomplish.

 

[jdraw]

To all,

I've done a little googling and found a Linnworks link
http://www.linnworks.com/Doc/linnworks_installation_and_setup

There is an online support forum at http://www.linnsystems.com/supportforum/

This seems to be an extensive, proprietary, commercial Stock and Inventory Management application. I did not find a complete model of the pieces.

It appears to me that Dylan or his company has acquired some or all of the Linnworks application.

It says, in the install section,

There are two types of packages available

Linnworks Anywhere
Linnworks Local Instance (Deprecated 8th July 2013)

which may be an issue??

I'm not sure if this was purchased based on Dylan's company's requirements for inventory management. I don't know anything about Linnworks other than this googling.
And I don't have time to read it all at the moment.

Perhaps Dylan can fill us in on
-WHAT his company does with the product
-WHAT his role is and
-WHAT exactly is driving a change in usage of the product
-WHAT he/they are trying to accomplish?

As I mentioned above, it is a proprietary application. I tried to find data structure info and the best I found was based on a link in the forum. Someone requested a data structure diagram. Another suggested to download express and look at the tables

The info from Linnworks is at this link http://linnworks.com/Doc/Database_Structure

More info from the forum post (dated 2013)
http://www.linnsystems.com/supportforum/viewtopic.php?f=8&t=4194

 

[Twincam]

I think I get where you're coming from as I wrote something similar in a previous life. I separated attributes and variants to make it simpler, so you could not have a default value for a variant.

Stock
StockID, Description
37, Trousers

Attribute
StockID, Description, Value
37, Material, Linen

Variant
StockID, Description, Value
37, Size, 28
37, Size, 29
37, Size, 30
37, Colour, Blue
37, Colour, Green
37, Colour, Red

Then join stock to attributes, then join to variants (grouped to remove value) then join to variants again on stockid AND variant desciption. You have to join to variants twice as variants are "multiplied".

That's just a quick summary to see if I'm in more-or-less the right area here???

 

[GinaWhipp]

Hmm, I *think* you are trying to build a house without going inside and looking at the floor plan. It looks good from the outside and you went and broke ground. You have looked at their database from the User interface and your spreadsheets and are now trying to build a database... sorry, but it's not going to work that way.

Everyone here is trying to help but we can only do so if you tell us what industry, what real world business this is and what you are trying to accomplish, i.e.:

I want to store Widgets we manufacture. These Widgets come in all shapes and sizes, as well as, belong to different industries some belong in commercial buildings while other belong in residential buildings. I also need to track where I get the supplies to make the Widgets which means I will also need to track Vendors. Oh, and I might also want to track where and to whom I ship my Widgets.

From the above, we will be able to build you a comprehensive relational database model. Another thing to keep in mind, how the data is displayed has nothing to do with how it is stored. The same way how the house is built has nothing to do with how it is decorated including what color siding you put on it.

So, please help us help you by telling us what you want to do... in good old plain English. Forget about the database, the Excel spreadsheet and just tell us about the house... ah, I mean your business.

 

[dylanpedro]

Thank you for all the advice everyone, I'm going to try and explain what we have and hopefully someone can help me find out where to start. Twincam your method is how I thought of doing it having separate tables but wanted to see if it was possible to have one table to hold all attributes/variations.

Basically the company I work for sell products on multiple channels such as their own website, Amazon, eBay etc.

All the data required is currently held on multiple excel sheets and they have a master excel sheet which has pages and pages of coding done in VBA as that is my background, and what it does, is allow their data to be properly formatted into multiple CSV files that can then be imported into a program called Linnworks which helps download all the orders from each channel and link them to the inventory previously imported, but also helps list products onto these channels with tools built into Linnworks. So in a sense Linnworks is an application that sits in the middle to either help list products or help process orders, but is useless unless data has been imported into it in a format they require it in, which involves multiple imports in stages of different data which I can explain further down.

Now the problem is, the excel sheets have become full of repetitive data and changes are also becoming hard to track therefore it has been advised, that a database be created in Access to allow the in house inventory data to be moved out of the excel sheets and into an Access database but using correct relationships and table layouts.

Right, now this is where I explain the companies inventory itself.

Products come in different variations, e.g colours, size, shapes and so on but essentially these are considered as one product line and therefore called child products that have a sku (Stock Keeping Unit) such as 123RED,123BLU etc. These products have a parent sku such as 123ALL assigned to them. These products can also be part of many different parents for example parent 321ALL can have products 123RED and 123BLU also assigned to it but also have its own child products such as 321GRE, 321YEL etc, which weren't assigned to the 123ALL parent product. So in simple terms one parent can have many child products and a child products can be assigned to many different parents.

Now on top of this, each product can have other attributes assigned to them that help describe them e.g Brand, Material, Size. All the valid values for these attributes are available from a dropdown in excel at the moment. Now this is where things get a bit confusing, as when the company lists products on some channels they list it in the following arrangement:
Parent Listing (Listing Title, Descriptions,etc)
-Child Red
-Child Blue
etc

But on some channels each product is listed individually as they do not allow variation so you have no parent item but instead individual listings e.g.
-Child Red (Listing Title, Descriptions,etc)
-Child Blue (Listing Title, Descriptions,etc)

as you can see my dilema is, how do I design a database that stores this data that covers all these scenarios?

On top of this if there is a table that hold attribute values, how can it hold values for both child products and parent products if they both exist in different tables? This is just the basics, each product can have multiple prices for different countries and multiple description, titles etc and I'm guessing this will require multiple tables to hold all this data?

When the data is prepared for Linnworks it needs the data imported as individual CSV files to make the correct mapping it requires as follows:

All physical products we have so:
SKU, Title, Price etc (headings)
123RED, Red Tracksuit, 9.99
123BLUE, Blue Tracksuit, 9.99
321GRE, Green Jacket, 15.99
321YEL, Yellow Jacket, 15.99

Then a parent SKU import so:
ParentSKU, ParentGroupName (headings)
123ALL, Tracksuits
321ALL, Jackets

Then another import to map the parent to child SKU's so
ParentSKU, SKU (headings)
123ALL, 123RED
123ALL, 123BLU

321ALL, 321GRE
321ALL, 321YEL
321ALL, 123RED
321ALL, 123BLU

as you can see from the above the previous 123RED and 123BLU are in two different parent groups 123ALL and 321ALL.

What I think we need is a way to have the ability to allow multiple childs belong to multiple parents as discussed above. We then need a way to have values form a dropdown lookup list to be assigned to parents or childs e.g a brand can be applied to a parent for listings that allow variations but also be applied to childs that have to be listed separately or a products that have no other variations. I cant figure out how these unlimited attributes that can be dependent on the product will be stored.

Before I carry on anymore can anyone explain if any of this even makes any sense?