Solved

Database Structure

Posted on 2015-01-18
8
109 Views
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.
0
Comment
Question by:Rob4077
8 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
Comment Utility
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)
---------
ID
Name
Description
DefaultColor

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

tAssembly
------------------
ID
AssemblyName
AssemblyDesc

tAssemblyItems
---------------------
ID
AssemblyID
ItemID
Quantity
DefaultColor

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

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

tAssembly:
ID    AssemblyName      AssemblyDesc
1     Wall Switch              Single Wall Switch
2     Double Switch         Double Wall Switch
tAssemblyItems
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).
0
 
LVL 14

Assisted Solution

by:Don Thomson
Don Thomson earned 50 total points
Comment Utility
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.  http://speedypos.wws5.com/
0
 
LVL 57

Assisted Solution

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

Jim.
0
 

Author Comment

by:Rob4077
Comment Utility
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.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Rob4077
Comment Utility
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.
0
 

Author Comment

by:Rob4077
Comment Utility
I will close the question tomorrow in case anyone wants to add more comments.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
OK then for a working sample of what Scott suggested, see:

http://www.experts-exchange.com/Database/MS_Access/A_19-BOM-parts-list-expansion.html

Which will get you started.

Jim.
0
 

Author Closing Comment

by:Rob4077
Comment Utility
I tried to apportion points based on contribution to achieving my objective. Thanks to everyone for input
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now