How to model a field that manifests itself in two ways

[Ayat]

Here is my problem:

Say I have a products table:

PRODUCT
========
Prod_id
Prod_name
Dept
Type
:


It is now possible that some of the products can also be a product
group, and a product group would then consist of multiple products.
(Note: if a product is a product group as well, both of them have
the same names. therefore for consistency, if in future someone
alters the product name, that should be reflected for the product
group if that product happens to be a product group as well)

So, I started of by thinking that the products table should contain
a binary filed like "Is_this_a_ProductGroup", where for any product
that is also a product group, while creating or editing the product
the user would flag this field "Yes" and "No" otherwise.

S, my revised products table is like:

PRODUCT
========
Prod_id
Prod_name
Is_it_a_ProductGroup
Dept
Type
:


Based on some earlier suggestions in this forum, I tried to create a
junction table like

PRODUCT_PRODGROUP
=================
Prod_id
Prod_Group_id

and joined the Prod_id above to the Prod_idin PRODUCT table and
created a copy of the PRODUCT table and joined the Prod_Group_id to
Prod_id in the PRODUCT_Copy table.

I would like some help now on:

1. whether this design is really correct?

2. If so, how does one go about the process of data entry through
forms for these tables?

Any help is much appreciated. thank you

 

[Pat Hartman]

This is a very common problem. The solution is to use a relation table to build the relationships. All products are stored in the product table whether they are components or assemblies. The relation table defines whether or not a product is a product group or part of a product group. No "flag" is stored in the PRODUCT table.

PRODUCT
========
Prod_id
Prod_name
Dept
Type
:

PRODUCTGROUP
============
Group_Prod_id (foreign key to PRODUCT.Prod_id) (primary key fld1)
Prod_id (foreign key to PRODUCT.Prod_id) (primary key fld2)
: