Hi I am trying to design a database. The first table/s are needed to hold a list of items and would have
HT-REF A unique alpha-numeric. HT-001, HT-002 etc.
MANUFACTURER This would be a lookup table to the MANUFACTURERS TABLE as it would repeat (many items from the same manufacturer)
DESCRIPTION This would be a shortform description of the item

The bit I am stuck on is this. Although each HT-REF is unique it can have a suffix added to specify what it is made of.
HT-001A is a bag made of single layer paper. HT-001B is a bag made of double layer paper. HT-001C is a bag made of 3 layers of paper. The HT-001 describes what machine the bag will fit. The suffix describes the bags construction. I guess there would be a table of bag types linked to the first table.

Add to this a suppliers price list for each supplier of each type of bag. It starts to get very complicated. I have tried various ways, but cannot find a way to do it.

Help please!!!

In your base table, store each part in separate columns.

If possible, store the most useful information you can rather than just codes.

Next, create a view that includes all the original columns as well as a calculated column that figures out what the code should be.

That way you get the best of both worlds: the proper code is kept up to date and you still have the original information to refer to easily.