Database Structure

Posted on 2015-01-18
Last Modified: 2015-01-19
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.
Question by:Rob4077
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 84

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 40556103
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).
LVL 14

Assisted Solution

by:Don Thomson
Don Thomson earned 50 total points
ID: 40556113
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.
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 200 total points
ID: 40556149
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.

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 40556156
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.

Author Comment

ID: 40556162
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.

Author Comment

ID: 40556163
I will close the question tomorrow in case anyone wants to add more comments.
LVL 57
ID: 40556191
OK then for a working sample of what Scott suggested, see:

Which will get you started.


Author Closing Comment

ID: 40557422
I tried to apportion points based on contribution to achieving my objective. Thanks to everyone for input

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question