Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-06-2017, 03:15 AM   #1
MattioMatt
Newly Registered User
 
Join Date: Apr 2017
Posts: 58
Thanks: 50
Thanked 0 Times in 0 Posts
MattioMatt is on a distinguished road
Table Structure for Products, Sub Products & Versions

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

MattioMatt is offline   Reply With Quote
Old 10-06-2017, 03:43 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 4,682
Thanks: 97
Thanked 1,279 Times in 1,252 Posts
Minty has a spectacular aura about Minty has a spectacular aura about
Re: Table Structure for Products, Sub Products & Versions

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 ?
__________________
A little thanks goes a long way
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

(Please use the scales on the left if we have helped!) Mark threads as Solved once you have an answer.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
MattioMatt (10-06-2017)
Old 10-06-2017, 04:44 AM   #3
MattioMatt
Newly Registered User
 
Join Date: Apr 2017
Posts: 58
Thanks: 50
Thanked 0 Times in 0 Posts
MattioMatt is on a distinguished road
Re: Table Structure for Products, Sub Products & Versions

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

MattioMatt is offline   Reply With Quote
Old 10-06-2017, 05:29 AM   #4
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 4,682
Thanks: 97
Thanked 1,279 Times in 1,252 Posts
Minty has a spectacular aura about Minty has a spectacular aura about
Re: Table Structure for Products, Sub Products & Versions

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.
__________________
A little thanks goes a long way
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

(Please use the scales on the left if we have helped!) Mark threads as Solved once you have an answer.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
MattioMatt (10-06-2017)
Old 10-06-2017, 08:59 AM   #5
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 26,078
Thanks: 12
Thanked 1,147 Times in 1,089 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Table Structure for Products, Sub Products & Versions

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
MattioMatt (10-09-2017)
Old 10-09-2017, 08:30 AM   #6
MattioMatt
Newly Registered User
 
Join Date: Apr 2017
Posts: 58
Thanks: 50
Thanked 0 Times in 0 Posts
MattioMatt is on a distinguished road
Re: Table Structure for Products, Sub Products & Versions

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?
MattioMatt is offline   Reply With Quote
Old 10-09-2017, 01:39 PM   #7
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 26,078
Thanks: 12
Thanked 1,147 Times in 1,089 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Table Structure for Products, Sub Products & Versions

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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
MattioMatt (10-12-2017)
Old 10-12-2017, 06:10 AM   #8
MattioMatt
Newly Registered User
 
Join Date: Apr 2017
Posts: 58
Thanks: 50
Thanked 0 Times in 0 Posts
MattioMatt is on a distinguished road
Re: Table Structure for Products, Sub Products & Versions

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.
MattioMatt is offline   Reply With Quote
Old 10-12-2017, 07:59 AM   #9
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Posts: 399
Thanks: 6
Thanked 66 Times in 65 Posts
Mark_ is on a distinguished road
Re: Table Structure for Products, Sub Products & Versions

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.
Mark_ is offline   Reply With Quote
The Following User Says Thank You to Mark_ For This Useful Post:
MattioMatt (10-13-2017)
Old 10-13-2017, 12:23 AM   #10
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 4,682
Thanks: 97
Thanked 1,279 Times in 1,252 Posts
Minty has a spectacular aura about Minty has a spectacular aura about
Re: Table Structure for Products, Sub Products & Versions

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.
Attached Images
File Type: jpg Product_Table.jpg (81.8 KB, 82 views)
__________________
A little thanks goes a long way
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

(Please use the scales on the left if we have helped!) Mark threads as Solved once you have an answer.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
MattioMatt (10-13-2017)
Old 10-13-2017, 06:53 AM   #11
MattioMatt
Newly Registered User
 
Join Date: Apr 2017
Posts: 58
Thanks: 50
Thanked 0 Times in 0 Posts
MattioMatt is on a distinguished road
Re: Table Structure for Products, Sub Products & Versions

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!
MattioMatt is offline   Reply With Quote
Old 10-17-2017, 07:08 AM   #12
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 26,078
Thanks: 12
Thanked 1,147 Times in 1,089 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Table Structure for Products, Sub Products & Versions

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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Table structure for contract/product database - please comment Morten Tables 1 03-05-2010 05:16 AM
Table structure - not duplicating records audrey Tables 7 02-12-2007 10:43 AM
Project Management, Product Breakdown Structure mark curtis Modules & VBA 1 03-29-2001 02:06 PM
Product Breakdown Structure mark curtis Forms 1 02-13-2001 05:53 AM
Product Breakdown Structure mark curtis Reports 1 10-24-2000 08:53 AM




All times are GMT -8. The time now is 02:01 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World