Database Structure

I've been asked to create a quoting and ordering system for the electrical firm I work for and seek some suggestions on database structure.

The business receives a house electrical plan. From that a user needs to count various components. One component that represents a significant level of complexity is a Single Light switch. From the plan we count how many there are but the user also needs to specify the following to enable the right components to be purchased:
1. Brand
2. Colour of back plate
3. Colour of face plate
4. Colour of switch mechanism
We also need to add a wall box for each light switch as an additional order component but there should be no need for the user to enter this component as it is automatically applicable to every light switch (no matter how many switches on it) and every GPO. Similar choices need to be made for GPOs

I don't want to present the user with a mile long list of part numbers to choose from. I want a basic form that lists all the standard components on a typical plan, e.g. single light switches, double light switches, 3 gang light switches....6 gang light switches, single GPO, Double GPOs, etc. When the user enters a number against one of the entries the system needs to somehow present a default and allow the user to amend the brand and colour options. The system also needs to add in the standard associated components such as the wall boxes. From this I should be able to fairly easily create a quote for the customer, an order for the supplier, an order and picking list for the person running the wires and an order and picking list for the person installing the fittings.

I am after some suggestions on how to present this and how to structure the associated tables.
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
I assume you'd have a table to store the core "items", like a single gang box, single switch, 20 amp socket, etc:

tItems (or whatever you want to call it)

You'd then have a table of "assembly" items, which would essentially list all the components of a selection:



So you could add the following to a "Single Gang Wall Switch" Assembly:

ID  Name                        Description               DefaultColor
1    Wall Switch              Single Wall Switch    White
2    Single Gang Box     Single Gang Box        White
3    Double Gang Box   Double Gang Box     White

ID    AssemblyName      AssemblyDesc
1     Wall Switch              Single Wall Switch
2     Double Switch         Double Wall Switch
ID    AssemblyID   ItemID   Quantity   DefaultColor
1       1                     1               1               White
2       1                     2               1               White
3       2                     1               2               White
4       2                     3               1               White

Open in new window

From there, the user could select an Assembly, and your code could grab all the AssemblyItems, and present the user with an interface where they could describe the various characteristics of the items. You'd then move those selections to an "Order" table (or Quote, perhaps).
Don ThomsonConnect With a Mentor Commented:
How many different components will you eventually end up having on this database. I it is going to be several hundred you really should be using a commercial program that does all this for you.  These programs are typically POS or point of sales programs.  Most of these programs are configurable for most types of businesses.

Most of them are either signal or multi-user.  They are generally priced per user and while they are not cheap, they usually include regular updates and remote support for an annual fee.  They may seem like they are very pricey but in the long run, they will save you hundreds if not thousands of hour of programming and testing.

The feature to do what you are asking for is a "Kit".   With a kit, the user selects an "assembly" that includes all the components. When you select an assembly, it generally pick all the parts that are needed and takes them out of inventory or creates a PO that can be used to price and order what you need.

Here is a typical program that is reasonably priced but will do what you are looking for.  You can download a demo version of it to try.
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor PresidentCommented:
Scott has gotten your started, but I think you may want to think more along the lines of what DTH was suggesting and look for a commercial program, but not for the reason given.

Access certainly can handle more than a few hundred assemblies, but the database design and the amount of work that you need to do to get want you want is fairly extensive.

What you need basically is a mfg app (items, assemblies, etc), but you also need what's called configurable orders and quotes.  

There are ways to shortcut what you want to do, but you've indicated (and I think already understand the issue I'm talking about) that you don't want the user to be presented with long part lists.   And that's the sticking point because the more product knowledge logic you build into a app, the less complex it gets for the user.

 If you want to do this, then I would start with what Scott suggested; an item table and assembly table.

 The simplest way to do the configurable part is to treat each order as an assembly itself, so you have a "temp" assembly (one for each order) vs the standard ones you create.  I did this for a custom book binding business and it worked very well.

 One of the tricky parts for you is the color.   You can certainly take the short cut of just putting it in the description, and letting the user choose the correct colors throughout.

But you could go a bit further and make color an attribute of the items, then filter parts list based on the color.   User still might pick the wrong color though.

 But then you could go even further and make color part of the order.  Now they would only be presented with parts of the right color.

 There are other issues as well.  For example, if a circuit is 15amps and they pick a 30amp socket, are you going to stop them?

This is what I meant by trying to build product knowledge in.  The more logic rules you have about what can go with what in a configuration, the harder it gets.  

The simplest route app wise is to let the user decide and that I think is really doable.   But if your not going to do that, your going to spend a lot of time on this as DTH said.   I think for you, you could manage the color OK, but beyond that the person entering an order is going to need the knowledge.

If you do look around for a commercial package, make *sure* it has the feature of configurable orders.   That used to be a feature only found in high end packages (because it's the most complex to pull off), but I haven't shopped Mfg packages in many years.   One I know of that's Access based is EZ MRP, but I'm not sure if it has configurable orders in it.

 Also, I would find it hard not to believe at this point that something like this doesn't already exist within the construction industry.   As DTH said, you may come out ahead just buying something rather than trying to build it.

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Rob4077Author Commented:
DTHConsulting, thanks for your observations. Whilst I agree that your suggestion would provide a more robust solution, I can't run with it. Before I joined the company they signed up for one of these packages but no one was around who could adequately implement and manage it properly. Unfortunately the owner then decided he no longer wanted to utilise it and when I joined he asked me to write a small, customised application to serve his specific needs.

Scott, thanks for the clear explanation and sample structure. Makes good sense, seems relatively easy to implement and provides the basis for a flexible structure.  Appreciate your help.  I will develop it along the lines you've suggested.
Rob4077Author Commented:
Hi Jim,

Just saw your comments and thank you too for the additional insights.

Actually there is a program that is widely used in the industry here that does what is needed. Costs about $700 per month. It's the one the business signed up for before I joined, and they let it go before I even looked at it. Apparently configuration was difficult and training requirements for users were high so the owner lost interest.

Fortunately the business only has a very small customer base, 3 major builders of project homes. To keep the price down the builders don't offer a lot of choice and the people who will be using the system are already very familiar with the current range of products so all I need to do is make it relatively easy to select. Right now they use spreadsheets  but it means they use one to create a quote, another to create the parts order and yet another to create a picking list for each part of the job.

The basic structure  DTH provided with the additional insights you added will help. And its reassuring to note that you both agree on what approach I should take.
Rob4077Author Commented:
I will close the question tomorrow in case anyone wants to add more comments.
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
OK then for a working sample of what Scott suggested, see:

Which will get you started.

Rob4077Author Commented:
I tried to apportion points based on contribution to achieving my objective. Thanks to everyone for input
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.