DMW logo
tel 01732 833085
tonbridge · kent · UK

Access Data Tables for Products—Goods and Services—Databases

Planning and creating a Microsfot Access products database tables and their relationships for a business that provides goods or services or both.

Last updated on 2019-01-11 by David Wallis.


Preface

A testing challenge for a developer is the creation of a products/services database. For businesses in the same sector, each business seeks to provide something — a unique selling point (USP) — that distinguishes it from its competitors.

This means its database must be constructed to support data that embody the properties of the goods and services determining that business's USP. In this piece I set out some thoughts on how to plan for and impliment such a database, using Microsoft Access for its construction. I’ll extend this page if it gains sufficient interest.

But before continuing, a caution. The data model you adopt for products will depend on the specific nature of the business your database is to serve. No single model will suit all businesses. Treat what I’m describing on this page as a starting point for your consideration of what you need for your database. On reflection you may come to the conclusion that what you need is a modified or different model. So test your data model thoroughly before adopting it.


Goods or Services

You need to be certain whether your database is to cater for goods or services or both:


Product and Product Class Entities

Product schems

I’m making product the entity that identifies each good or service that a business markets.

I’ll start any database with this products schema, even when my client is convinced they are “never” going to sell both goods and services. (Simplify the schema. If you dare.)

Because of the different nature of goods and services, it follows that a product may be either a good or a service, but not a bit of both. I’ll use the product class entity to distinguish a product as either a “good” or a “service”.

Product class Comfy Bird Boxes Ltd (CBBL) currently sells bird boxes, bird tables and bird baths.

If you want them to, CBBL will fix their bird boxes to your tree, wall, fence, or whatever, and offer a repair service for all their goods.

You can imagine CBBL’s website navigation bar:

CBBL nav bar


Product and Product Class Tables

At this point we can establish the first of our Access database tables:

Product tables

tluProductClass can furnish combo boxes on your database’s user-interface forms with which a user can assign good or service class to a product.

Why go the length of providing a whole table to product class? Why not have an option column in tblProduct that is used to distinguish between good and service? Well, that may suit DBBL’s business, but consider you’re creating a database for a company that manufactures, sells and maintains road sweepers.

This company markets not only complete road sweepers, but also sub-assemblies (like gear boxes) and individual parts (like M10 25 mm stainless-steel nuts and bolts). tluProductClass allows “Good” to be replaced by three records: “Complete Good”, “Sub Assembly” and “Part”. This without you needing to change the user interface, or possibly the design of your product table.


Product Grouping

Most businesses group, or categorise, their goods and services under headings that suit cataloguing, advertising, filing, warehousing and so on. CBBL, for instance, group their Wren and Robin houses under "Bird Boxes". One of my clients, an insurance broker, had categories for their services including “Domestic Insurance”, “Commercial Insurance” and “Life Insurance”.

I apply the entity product group to accommodate grouping. This table arrangement provides for grouping and puts the management of this in the hands of the database administrator, without the need for intervention from the developer:

Product grouping

An Access select query based on these three tables yields this data set:

Product group query


Product Properties

Products, particulary goods, have properties (characteristics, features, attributes, etc) like dimensions, colour, quality and size. Why not record these properties in tblProduct?

Well, consider CBBL’s products. Wren and Robin boxes have the dimensions of weight, height, width and depth; bird baths of weight, height and diameter. Additionally, bird boxes have colour. If you make properties part of tblProduct, that’s six additional columns — for weight, height, width, depth, diameter and colour.

Ah! But there are six colours in CBBL’s current catalogue of bird boxes! So, extra columns to accommodate these!

Hence, these are some of the pitfalls of recording properties in tblProduct:

One data model for product properties is to divide properties between two entities, namely dimensions and features.


Product Dimensions and Features

My dimensions entity applies to product properties that can be recorded as numeric values (numeric data type): the properties “height”, “width”, “depth” and “weight”, for example.

My features entity applies to product properties that are recorded as alpha-numerics (text data type). “Colour” and “Size” are examples. Size here covering the product properties of “small”, “medium” and “large”.

These are my tables and their relationships for this data model:

Product properties tables


Product Dimensions

tblDimensiontluDimension contains the list of dimensions that can be assigned to products.

tblDimensiontluDimensionUnit lists all the units that may be used in the measurement of these dimensions.

Both these tables will furnish combo boxes on user-interface forms and provide the database administrator a means of adding any other dimensions that may be needed.

tblProductDimension allots dimensions to products:

tblProductDimension

Using the three tables tluDimension, tluDimensionUnit and tblProductDimension as its record source, a select query, qsProductDimensions, makes the dimension records comprehensible:

qsProductDimension

Note that the users have more work to do inputting dimensions. Though they recognise they need to record a height for the Robin Box, they have left the value blank, perhaps as a reminder that someone has to get out their ruler. Otherwise they have entered records only for those dimension for which they have values.

Now, one row for each dimension for each product is not an ideal way of presenting the information for reporting, say. Perhaps best to use a cross-tab query, qctProductDimensions, to put everything on one line:

qsProductDimension

This is qctProductDimensions’s SQL:

Cross-tabl SQL


Product Features

tluFeaturetluFeature contains the list of features that can be assigned to products.

In the data model above, I’ve given the feature identity a simpler treatment than the dimension entity by limiting it to a sinlge look-up table:

Product features tables

tblProductFeature allots features to products:

tblProductFeature

A select query, qsProductFeature, makes the feature records comprehensible:

qsProductFeature


Your Support for dmw TIPS

If you’re going to make use of Tips in any of your professional or commercial work, then please consider making a payment in recognition of the time spent publishing this website.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —

To say how much you want to pay and to receive
a tax invoice for a GBP (£ sterling) contribution —

Invoice

Thanks, in anticipation.

SSL Cerification

“The best Products are customized not standardized.”

Carsten K Rath, German entrepreneur.