DMW logo
tel 01732 833085
tonbridge · kent · UK

How to Plan for the Tables of an Access Products Database

How to plan a data model of the tables and their relationships for a Microsoft® Access products (goods and services) database.

Last updated on 2020-05-26 by David Wallis.


Preface

Any business seeks to provide something — a USP (unique selling point) — that distinguishes it from its competitors. This means that, in addition to managing data for day-to-day operations, the business’s database must support data that embody the properties of the goods and services determining that business’s USP.

In this article I set out my thoughts on how to plan for and implement such a database, using Microsoft Access for its construction.

I’ll extend this page if it gains sufficient interest. But first, 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. Rigorously assess and refine your data model 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, particularly 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 boxes 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

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

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

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

If you’d like an invoice to account for your donation, let me know how much you’re donating —

Invoice

Thanks, in anticipation.

SSL Cerification

“The best Products are customized not standardized.”

Carsten K Rath, German entrepreneur.