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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
I will close the question tomorrow in case anyone wants to add more comments.
OK then for a working sample of what Scott suggested, see:
https://www.experts-exchange.com/Database/MS_Access/A_19-BOM-parts-list-expansion.html
Which will get you started.
Jim.
https://www.experts-exchange.com/Database/MS_Access/A_19-BOM-parts-list-expansion.html
Which will get you started.
Jim.
ASKER
I tried to apportion points based on contribution to achieving my objective. Thanks to everyone for input
ASKER
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.