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 2024-05-19.

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:

• What's the difference between goods and services? A popular description of goods is they're tangible and discernible items — physical things that can be touched. Of services, they're the provision of essentially intangible benefits — e.g. consultancy, training, sports massage — where people's time, expertise, or both, is for sale.
• Does your client consider there to be a difference? Sometimes, people use the terms “goods” and “services” loosely or interchangeably. For some of the databases I've created, I had to make the distinction for purposes of defining the data model, even though the user interface adhered to the client's usage of the terms.
• Why the concern over the difference? Well, experience will tell you that there are implications for it's design that may not be explicitly stated in your client's specification for their database.

For example, your client may regard their “service” to be the “supply of bird boxes”; but you know what they're actually supplying is goods — the bird boxes they manufacture — and that therefore the database might eventually have to provide for returns. (It's improbable that you'd need to cater for returns on a service: I can't imagine how anyone could return a sports massage session.)

Product and Product Class Entities

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”.

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:

Product and Product Class Tables

At this point we can establish the first of our Access database 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:

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

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:

• The column count mounts to accommodate additional properties
• Not every product has all the properties, so much of the table contains empty space
• Each time a new product is introduced, an additional suite of property columns need to be recorded. Hence the developer has to extend the table and possibly the associated queries, forms and reports.

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 Dimensions

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

tluDimensionUnit 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:

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

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:

This is qctProductDimensions's SQL:

Product Features

tluFeature 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:

tblProductFeature allots features to products:

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

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) —

Thanks, in anticipation.