Tricky One

[Bert666]

Hi there people,

I am facing a really trick situation - that has me baffled.

I am creating a database for a Home Textile Firm and the problems i am facing are :

1. The products have very different attributes e.g.

A Flat Sheet will have Width, Length, Drop, Hem
A Sham will have Width, Length, Flange, Hem, Flap
A Pliiow will have Width, Length, Hem,
A Bed Skirt will have Width, Length, Drop, Pleats, Size of Pleat

Now, one way of doing it is to have all the attributes and just the ones that the product relates to will be filled in (But that way i will be left with many Nulls which i don't think is the best way to do it)

2. Each product has a different formula of calculating the fabric width required and the rating for the fabric. How to i have these formulas calculating accordingly. One way is to create a separate form for each product (But again this is inefficient - since each time a new product is added - mean a new form needs to be created)

Formula Example (Bed Skirt) 60 x 80 with 14" Drop and 4 Pleats each Pleat 2"

60 + 2" Seam = 62
80 + 2" Seam = 82
-----------------------
62" for 1 side = 62"
82" for 2 sides = 82" ' Since Skirts cover 3 sides
= 226
4 Pleats * (2" * 4) = 32
= 258 / 39.37 = 6.55 meters
and since the drop is 14" + 1 for Seam = 15 " Fabric Width



Any suggestion would be highly appreciated.

Bert

 

[Jon K]

I think the problem that you are facing is whether to choose ease of use or to follow table normalization.

I would choose ease of use and put everything in a flat table, one record for one product. (See the attached database.)

On an input form, I would enter the necessary attributes for a product, build the formulas and click on a command button to evaluate the formulas. For your sample data, the Bed Skirt formulas entered on the form would be:-

1. ((Width+2)+(Length+2)*2+NoOfPleats*(PleatSize*4))/39.37
2. (Drop+1)​

When the button is clicked, its On Click event would pass all the attributes from the record to a public function getFabric(), which is stored in a Module:

Public Function getFabric(Formula, Width, Length, Drop, NoOfPleats, _
       PleatSize, Flange, Hem, Flap) As String
   
   Dim F As String
   
   F = Formula
   F = Replace(F, "Width", Width)
   F = Replace(F, "Length", Length)
   F = Replace(F, "Drop", Drop)
   F = Replace(F, "NoOfPleats", NoOfPleats)
   F = Replace(F, "PleatSize", PleatSize)
   F = Replace(F, "Flange", Flange)
   F = Replace(F, "Hem", Hem)
   F = Replace(F, "Flap", Flap)
   
   ' evaluate formula and round to 2 places of decimal.
   getFabric = Round(Eval(F), 2)
   
End Function

The function would replace the field names in the formulas with their values, then evaluate the formulas and return the required length and width of the fabric to the form.

Note:
If the formulas for each type of product are fixed, you can replace the formula text boxes with combo boxes for the user to choose the formulas rather than typing them.

The code of the getFabric function in the Access 2000 version is neater as we can use the built-in Replace() and Round() functions. These two functions are not available in Access 97, so we have to use some code instead.

 

[The_Doc_Man]

I've thought about this one and it is a prime example of why total normalization is not ALWAYS a good idea.

To normalize it totally, you would have a table of attributes to go with your product tables. Your product tables would provide a code for a SELECT of all attributes that applied to your product in a particular context. The attributes would have a product ID, an attribute code, and a value, multiple records in the details table corresponding to the number of attributes for that particular ID. Computing your fabric requirements would be a nightmare with that structure because you need everything in a flat record that total normalization just isn't going to provide.

But partial normalization might not be so bad... just somewhat ugly. It is not how many products you have but how many basic types you have that will drive this next suggestion.

For each class of product (defined as any subset of possible products that have the same exact attributes, though the individual values of the attributes may differ), identify a product code. Your suggested product classes are FLAT SHEETS, SHAMS, PILLOWS, BED SKIRTS, etc.

Create a single product table with all possible attributes as fields. Also include a product class-code and your catalog ID as distinct fields. The data in this table will be sparse (specific fields are not always populated).

Now create several SELECT queries, one for each possible class-code but not one for each product. Perform your fabric computations in the queries specific to each class code.

Finally, to bring it all together, you need a UNION query that selects the product's catalog ID and the results of the fabric computations for each different class code. In this UNION query, it is important to always return a value for each possible computation, but sometimes you can return a constant of 0 when it is not applicable. Each new product CLASS will require a new entry in the UNION query, but not each new product.

How do you use this? As a lookup on the displayed form or report, you can pick up the fabric requirements from the union query that is linked to the product ID.

Another suggestion: On forms where you are defining new products, you can of course have the AfterUpdate event for the class-code go around the form and disable any field for which a value is not applicable. Set the default for everything numeric to 0, not null, if you are going to do this.

 

[Bert666]

Thanks Guys

Thanks guys,

Help Really appreciated

Bert.