Table design with automation of materials combinations

[tpeene]

I have a challenge in designing a database and I'll explain the user case.I need to make a database for calculations on specific types of walls and floors. If a user enters some details the database will have to come up with a selection of walls that match the criteria and the user can then choose one and move on to the next. Projects consist out of tens to hundreds of different situations.

Users will enter parameters in different fields, such as length, width, height and also desired sound insulation, insulation values for temperature and so on. Based upon these values calculations need to be made to come up with a bill of materials and a price per item.

One wall will consist out of different materials like drywall which is calculated in surface area, but also studs which are calculated in length and insulation which is calculated in volume. Walls are only certified for specific sizes and a bigger size can mean a different type of wall is needed.

These certification values will be added to the database by users.



I'm having trouble getting started on this database. I'm unsure if I should store all materials in one table or if I should keep them separate by volume/surface area/length. It's a little overwhelming to get started on this. I've built bills of materials before but in these the user would manually add items but now these are all dynamic. Can you give me also me pointers in the right direction?

[kd2017]

We're going to need a lot more details to go on.

So you have a predefined list of wall types consisting of drywall spec, stud spec, and insulation spec. These predefined wall types have predetermined r values and such that the user can search against?
Or does that need to be calculated? What are the formulas you use?

[tpeene]

For example a certain type of wall can be used when conditions are met, so the thickness will have to be between certain values the user fills in, but it can also only be used up to a certain height. Some will need foam insulation for certain edges and that are different calculations.

I can store materials with different attributes like volume, length and surface area all in one row or I could make a table for insulation, one for foam, one for boards and so on. I'm going to use queries to select a range of walls from a table that meets values of fields the user fills in and each wall will be calculated based upon specs the user has provided, including foam, plaster, boards, lengths and all those things. I'm curious how you would setup the materials in tables.

[kd2017]

I'm leaning towards something like [wall_configuration] has many [material] has many [material_attribute]... just as a start

The tricky part is figuring out a robust way to configure the attribute table such that all the necessary calculations are easy to make.
Be careful here that your brain doesn't slip into spreadsheet mode when designing these tables. That is don't make really wide tables with lots of columns.

No, I don't think I would make a separate table for each different type of material.

Do you currently use spreadsheets or anything to do all this? If so can we see some examples?

(Edit)
Are you wanting your application to calculate wall configurations on the fly or are your wall configurations predefined?

[kd2017]

@mike60smart,
Don't you think it would be more valuable to the community and to OP to keep things in the forum so we can all take a crack at it?

[mike60smart]

Hi kd2017
I agree, but sometimes OP's prefer to send a copy rather than uploading to the forum.

[kd2017]

I can see why it might appear that way when you're constantly pming folks and encouraging them to continue their conversations in private...

[tpeene]

Thanks gor your reply. You can find a selection of walls here: https://www.gyproc.nl/systemen/zoeke...steemtype%3A79

Users will enter some or all of the same criteria and the same selection should appear, along with calculated costs based on size entered by the users, time to build, list of materials and so on. The predefined options will be loaded in the database for this brand and many more.

It's funny you mention spreadsheets. Right now every single wall is calculated in a spreadsheet by hand, researched every time based on criteria given by a customer. I've been using excel a lot the past 15 years and I'm a little out of touch with access, but I think access should do better with this challenge.

I agree with as little tables as possible, I think I'll start with that first and take it from there. Assign many Materials and many Criteria to Walls and figure out how to calculate from there

[Edgar]

I was gonna help in the thread but since OP now has mike60smart's personal private help, I'm out.

Thanks for the heads up, Kd

[mike60smart]

Hi Edgar
I have not received any email from the OP so he has not had any private help.
If he did contact me then I would then publish my response to the Forum.

[tpeene]

I don't understand. I've replied in this topic but the reply is gone. Can't seem to find it in a private section either. There are no posts there. Never meant to hurt anyone's feelings but also noone asked me if I was privately replying and give me a chance to say something.

[tpeene]

https://www.gyproc.nl/systemen/zoeke...%5D=rtf%3A1518

This is an example of one brand. I'll enter these with all criteria in the database and users will enter some or all criteria and the database should hive them options to choose from, including options to switch some materials if they're out of stock.

I'm leaning towards many Attributes to many Materials to Walls. One project will consist of many walls but also ceilings, openings, etc.. right now it's in excel in the same way you would write it down on paper. One page per wall, on it the price per board, stud, insulation (manually updated daily) and the amount needed filled in by hand using a calculator. It's horrible.

[Edgar]

@tpeene
Let's move on. Have you tried anything?

There are countless ways to design this database. Let's see what you already can handle, maybe we can adapt to a style you'll be familiar with.

Here's a suggestion of tables:
Clients.
Projects. As parent for project products.
Project products. As children of a project, to store the products for that project.
Products. As parent for product compositions.
Product Types. To specify if it's a wall, a floor or something else.
Materials. To describe commercial materials and its variations.
Material Types. To describe a material family, it's handy, say, if suddenly the entire steel industry changes price and you need to query all steel materials to modify their price, etc.
Units. To describe the commercial presentation of a material.
Unit Types. Because you'll likely have to handle linear units, 2D units like Sq Ft or Sq meter, 3D units, weight units, integer units, etc.
Product Compositions. As children of a product, to store the materials each product is composed of in terms of 1 unit of the product. If your floor unit is sq meter, and you need 4 tiles for that, you specify 4 pieces, etc.
Dimensions. Because it's not the same if you're asking your user for a height or a depth, a length or a width, etc.
Dimension Sets. To store a set of dimensions used to describe a product. If 2D wall, you include Length and Height. If a 3D wall, you include also depth or width. If floor, X and Y. Etc.
Product Dimensions. To store the dimensions used to calculate the quantity of a product.

In this process, you will accurately determine the necessary materials for manufacturing a single unit of your product. To achieve this, refer to the product compositions table and include the respective materials and their quantities. Subsequently, employ the product dimensions to identify the dimensions utilized in calculating the overall quantity of the product required for your client's project. Finally, multiply the product composition by the outcome of the product dimensioning to obtain the total quantity of materials needed.


Hope this gives you a few ideas. Let us know.

[pbaldy]

There were a couple of moderated posts, now approved.

[tpeene]

Thanks for your advice! I've built all kinds of (commercial) programs in Access and I'm used to creating very complex databases but I haven't used it for the past ten or so years and in the mean time I've grown accustomed to Excel. I've used Access for maybe 15 years total so I hope I'll be up and running again soon. I'm going to start designing the database tomorrow, using your tips and I'll post an update when I'm done. Thanks again!!