Designing a form for multiple different-type products

[vasalnet]

I want to design a db containing products with different technical
characteristics.
Which is the best way to do this? All products should be stored in the
same table and only the product specific fields should be printed and
displayed.

Using a tab control in the forms (with a per-product tab) isnt a
solution since additional types of products may be added in the future
and current product types are ten.

Any good idea is welcomed!

9404<Designing a form for multiple different-type products>

 

[Jeff Boyce]

I'm not very clear on what you're trying to do. But my imagination is
working overtime!

I'm having trouble imagining a well-normalized data structure that would
handle "products with different technical characteristics". Does this mean
all products have, for example, length, width, height and weight (but each
product has different values), or are you saying you want to store technical
specs for automobiles, chainsaws and mechanical pencils? The former is
easy...<g>

When you say "all products should be stored in the same table", how did you
reach this decision? In a well-normalized relational database design,
decisions about table structure derive from the underlying entities and
attributes. Have you already been through this exercise, and if so, can you
provide an example?

Using a tab control in a form has nothing (OK, very little) to do with the
underlying data structure. In Access, tables store data, forms display it.
If you haven't already designed this database, how did you decide that a tab
control in a form will NOT do what you need done?

Would you also provide your definition of a "good idea"? <G>

 

[Guest]

Hi there.

I too am not 100% on what you need.

However, there are some things that will almost go without saying when
designing your database.

First, concentrate on how the data is stored before you plan on how to show
it.
In this instance, if you have unique products, then these can all be kept in
one table with their descriptions.

If, however, you have 5 types of hat (for example), you would have one table
to store specifics, and one to hold a "family" name. then you relate these
tables:


|FamilyName| |Product |
---------------- --------------
|FamilyID (PK)|------------ |ProdID (PK)|
|myName | | |myName |
--------------|Family (FK)|

Idea...?

Basically, if you are repeating any values in your tables, then they can be
"normalised".
This means that they are split into two (or more) tables, with relationships
between them.

To see the benefit, consider a database at work.
It stores all employee details: Pay, Manager name, Dept.
Imagine that one of the manager's left the business and their staff were
moved to other teams...

If everything is in one table, you would have to search through every record
for that manager and manually change it.

With a normalised structure, you can change it in the "managers" table, and
all changes will pass to any related records too.

Hope that this helps...


Danny

 

[John Vinson]

I want to design a db containing products with different technical
characteristics.
Which is the best way to do this? All products should be stored in the
same table and only the product specific fields should be printed and
displayed.

Using a tab control in the forms (with a per-product tab) isnt a
solution since additional types of products may be added in the future
and current product types are ten.

Any good idea is welcomed!

9404<Designing a form for multiple different-type products>

This is sort of duplicating other answers but... this may be one of
the rather rare cases in which one-to-one relationships are
appropriate, for "Subclassing".

This would have one table named Products, with a ProductID primary
key, and fields for information that is common to all products. This
table would be related one-to-one to additional tables, one for each
specific type of product (e.g. you'ld have a Printers table with
fields for PrinterType, PrintSpeed, MaxWidth, MaxLength, etc., and a
Monitors table with ScreenSize, Resolution, etc.)

Don't worry about *forms* for starters. Get the table design right
first, and then design the forms to fit the tables, rather than vice
versa!

John W. Vinson[MVP]

 

[vasalnet]

Lets clear some things i havent written correctly:
1. Different types of products means: keyboards, scanners, monitors
etc. each with different attributes.
2. I am aware about the difference og tables/forms/reports. By
splitting all non-common fields into different tables is the best (and
most wise) thing to do.
3. Lets leave the basic db design (tables and relations) and come to
the presentation part. Do you have any idea which is the smartest way
to display all relevant fields only, in a form and/or report?
For the forms making fields activated or not for editing based on the
type of product already selected by the user is the most obvious
solution, but not the most practical. And what about reports?
How should i do it?

A great thanx for all your support.

vasalnet

 

[John Vinson]

Lets clear some things i havent written correctly:
1. Different types of products means: keyboards, scanners, monitors
etc. each with different attributes.

ok... that's sort of what I was guessing.

2. I am aware about the difference og tables/forms/reports. By
splitting all non-common fields into different tables is the best (and
most wise) thing to do.

That's a judgement call. Some do it that way, some don't. In this case
I'd be inclined to do so.

3. Lets leave the basic db design (tables and relations) and come to
the presentation part. Do you have any idea which is the smartest way
to display all relevant fields only, in a form and/or report?

You could have VBA code on a mainform to set the SourceObject property
of a Subform to the name of a form appropriate for the current
record's product type. This would need to be done in both the form's
Current event (to choose the right subform for existing records) and
the AfterUpdate event of the control which selects the product type.

For the forms making fields activated or not for editing based on the
type of product already selected by the user is the most obvious
solution, but not the most practical. And what about reports?

A Report could be based on a query left-joining ALL of the subclasses;
their controls could be displayed in a section (set up with
sorting&grouping) with its Can Grow and Can Shrink properties set to
true. The irrelevant sections would simply not appear.

John W. Vinson[MVP]

 

[vasalnet]

I will try and let you know.
Thanx.

 

[vasalnet]

Thank you John W. Vinson!
You made my day. These were what i was looking for.
Thanx again!