Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Database Structure

Posted on 2015-01-18
8
Medium Priority
?
118 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
[X]
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
8 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 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)
---------
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 200 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.  http://speedypos.wws5.com/
0
 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 800 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.

Jim.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

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

Author Comment

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

Author Comment

by:Rob4077
ID: 40556163
I will close the question tomorrow in case anyone wants to add more comments.
0
 
LVL 58
ID: 40556191
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
ID: 40557422
I tried to apportion points based on contribution to achieving my objective. Thanks to everyone for input
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

715 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