Table Structure for Products, Sub Products & Versions (1 Viewer)

MattioMatt

Registered User.
Local time
Today, 20:58
Joined
Apr 25, 2017
Messages
99
Needing some help with the best practice with an approach to setting up some tables for a Product/Sub Product and Product Versions. I believe I’ve set it up wrong and trying to work out the best way to move forward.

Some background information..
  • A Product can have many Sub Products but also may not have one at all
  • A Product will have at least 1 version but could have many
  • Sub Products may have a version
  • The version of the sub product may differ to the version of the product it is linked to

Now, I have set the following up… I think because there is no link between the sub product and versions it’s not going to work as above with the requirements. I believe it only allows for a version linked to a product and not a sub version.

Product (Table Name)
Product_ID (PK)
Product Name

Product_Has_Sub_Product (Table Name)
Sub_Product_ID (PK)
Product_ID (FK to Product Table)
Sub_Product_Name

Product_Versions (Table Name)
Version_ID (PK)
Product_ID (FK to Product Table)
Version_Name

I have two thoughts, neither of which I am not sure is the right approach – was looking for some input into my thoughts below.

Thought 1:

Product_Has_SP_Has_Version (Table Name)
Product_Version_SP_ID (PK)
Product_ID (FK, Product Table)
Sub_Product_ID (FK to Product_Has_Sub_Product Table)
Version_ID (FK to Product_Versions Table)

Thought 2:

Product_Versions (Table Name)
Version_ID (PK)
Sub_Product_ID (FK to Product Table)
Version_Name
 

Minty

AWF VIP
Local time
Today, 20:58
Joined
Jul 26, 2013
Messages
10,355
Have you thought about simply having a single product table, and a "Is a child of" table listing the parent product ID and any child product id's?

I'm also not sure that a Product version should be stored in another table. If you have an ABC1 and an ABC1 Rev2 is it not really a different product?

You could have another table listing alternative part numbers e.g. Abc1 > ABC1 Rev 2 ?
 

MattioMatt

Registered User.
Local time
Today, 20:58
Joined
Apr 25, 2017
Messages
99
Hi Minty,
Thanks for your reply!
On your first point, I could use...?

Products (Table Name)
Product_ID (PK)
Product_Name
Product_Version

Sub_Products (Table Name)
Sub_Product_ID (PK)
Sub_Product_Name
Sub_Product_Version

Product_And_Sub_Products (Table Name)
Parent_Product_ID
Product_ID (FK)
Sub_Product_ID (FK)

Then when I am matching Product/SubProducts & versions to the customer I can then ust the Parent_Product_ID in that table?
My only concern is the amount of repetitive data in the Products Table due to the large amount of versions.

The reason I went for separating the product name and product versions is for user friendliness on the forms. If I leave the product and versions together there is 985 products records because of all of the versions. I didn’t want the end user to be scrolling through such a list in a combo box. I thought it would be easier by having two combo boxes on a form so if you click a product the next two combo boxes would update with the sub products versions relating to the product selected in the first combo box.
A second view was to try and remove the duplication for multiple rows of the same product name because the version is different.

Example:
Product Name Product Version
Matt’s Product v1
Matt’s Product v1.1
Matt’s Product v1.2
Matt’s Product v1.3
etc…

On your second point...
In a way, yes, they are different products, but they belong to the top-level product family – so the sub products are small variations to the existing base product. However, the sub product normally has a different name/purpose than the parent product but is still based on the parent product (confusing I know!)

Examples could be:
Product Name: Matt’s Product
Version: v2.3

Product: Matt’s Product
Version: v2.4
Sub Product: SuperMatt
Version: v2.4

Product: Gold Star Product
Version: v2.5
Sub Product: Return Matt’s Investment
 

Minty

AWF VIP
Local time
Today, 20:58
Joined
Jul 26, 2013
Messages
10,355
Can any sub product also be a main product?

I think what I'm getting at is, if the products and sub products are essentially the same things, then I wouldn't put them in two tables, as you'll end up with Product ABC1 as both a product and sub product, and then have the version issue on top of that.

The Junction table, Product_And_Sub_Products. would be the same, just storing the PK from the products table twice - as Main and Sub ID's.

If that's not the case, e.g. sub products are always only sub products, then your suggested layout is correct.

Versions - at the end of the day you know your business and how it works a great deal better than we do, so if you have many versions of the same product keeping it as a separate field within the product table makes sense.

That way you could group by the Product Name, then list the Versions to get to the correct PK ID.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:58
Joined
Feb 19, 2002
Messages
42,976
I agree with Minty regarding having a single product table. I've worked with a lot of manufacturing systems and one product table is the best sollution. The junction table just links one part to another from the same table rather than two separate tables. When you draw the relationship in the relationship window, you will need to add the product table twice. Access will suffix the second instance with _1 because it needs to have a unique name. This doesn't duplicate the table, it simply allows you to clearly define two separate relationships to the same table.
 

MattioMatt

Registered User.
Local time
Today, 20:58
Joined
Apr 25, 2017
Messages
99
The sub products won't be a main product.

With a single table do you mean all information in one table, so remove the Sub Product table and linking of products and sub products? (Apologies I'm just trying to double check I'm following correctly). Also I'm not sure I understand the requirement for two product tables in the relationship view - if there was to be a single product table what would it needed to be added to the relationship view twice for? (Again just trying to understand so I apologise)

If it were all to be in a single table, how can I control the drop down on screen better as my concern is the number of rows is going to be huge. I know you mentioned about grouping the product information Minty but if there is just one long list is that not going to be an option?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:58
Joined
Feb 19, 2002
Messages
42,976
If you want to distinguish between end-items (items which are sold) and components, do that with a flag in the record. Then adjust your queries as necessary when you need to select only end-items. If end-items are never components (ie never sold) then two tables would work but that is a business rule that could be changed. What happens if the company decides they want to be able to sell a component. It will be a nightmare to accommodate that change. When you drive a car your eyes are constantly moving. Looking for hazards and changing conditions. Use the same defensive techniques when designing applications. Never make a commitment that isn't necessary. I would give the same advice to someone building a system for a school. Teachers, administrators, students, and parents are all people and therefore should be stored in the same table. And, if you think about it objectively, a single person could actually fill four of those rolls at the same time. Same situation that you have. Even if today you don't have any overlap between components and end-items, doesn't mean that you won't tomorrow.
 

MattioMatt

Registered User.
Local time
Today, 20:58
Joined
Apr 25, 2017
Messages
99
Thanks Pat,

Your example has helped to make it clear. I see how it becomes complex trying to split them out which I believe is why I am hitting problems on how it should work.

Essentially I need to be looking at one table called Products that would include the following:

Product_ID | Product_Name | Sub_Product_Name | Version
----------------------------------------------------------------------
1 | ABC | null | 1.0
----------------------------------------------------------------------
2 | ABC | XYZ | 1.0
----------------------------------------------------------------------
3 | DEF | null | 1.4
-----------------------------------------------------------------------
4 | GHI | null | 2.6

I'm not sure I understand the point regarding a flag for the top level product. I presume this is to flag it as the top level product name some how?

In regards to the query, if I want to query to get a list of product names it's going to pull back a list with duplicates - I assume the way around this would be to make it a distinct list?
If I do that I am going to be excluding versions when I try to query to get the version as well?
Apologies - it's this bit I am getting hung up on as it was the reason I attempted to separate them.
 

Mark_

Longboard on the internet
Local time
Today, 13:58
Joined
Sep 12, 2017
Messages
2,111
MattioMatt,

I'd sit down and look at some business rules to help you determine some of your own answers;

1) Can more than one "Version" of the same "Product" be sold at the same time?
2) Do "Product/Version"s become outdated and no longer sold?

If yes to either, put in a field that identifies if the product is active or not. By personal preference I use a DATE for these kinds of situations so that I know when it became obsolete.

3) How many products are expected?
4) Do users normally deal with them by a "Category" prior to product?

If dealing with large numbers of products OR if users expect them to be sorted by Category, you may have to add a parent (could be in the same file) that is the category they wish to look for.

For your first example, this could be "All Matt's products".

Some times sitting and watching what the end users do and talking to them can give you great insight into how you need to set some of your system up.
 

Minty

AWF VIP
Local time
Today, 20:58
Joined
Jul 26, 2013
Messages
10,355
MattioMatt - that wasn't the structure that Pat and I were suggesting.
This was more my thoughts at a very basic level.


Then to create your sub product list you have a table that stores the ProductID fields in it twice.

tblSubProducts
ParentID, ChildID

This structure accommodates any number of products, any number of child/sub products, and although not the case yet means a product can be both a top level item or a sub product.
 

Attachments

  • Product_Table.jpg
    Product_Table.jpg
    81.8 KB · Views: 230

MattioMatt

Registered User.
Local time
Today, 20:58
Joined
Apr 25, 2017
Messages
99
Hi Minty,

Thank you for that graphical representation! It certainly helped!

I see what you mean now about the listing of the product ID's twice. It makes more more sense seeing it visually. I can see how this would work and how I can then I can query for information as well.

I cannot thank everyone who contributed enough - THANK YOU!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:58
Joined
Feb 19, 2002
Messages
42,976
sub_product_name does not belong in the product table. There is very little point in a structure that allows only a single child (sub product) per parent (product) - unless of course you are China.

To create the relationship between 1 product and multiple sub products, you will need a junction table. That will have as many rows per product as the product has sub products. So of prodA has 10 subproducts, there will be 10 rows
prodA, prod1
prodA, prod2
ProdA, prod3
etc.
 

Users who are viewing this thread

Top Bottom