Not sure how to normalize a table that will contain a enourmous amount of settings

[Joshin]

Hello, I am not entirely sure how to go about normalizing this table. It contains machine settings for manufacturing lines. Pretty much each line is broken down like this:

Each Line has quite a lot of settings ranging from 200-600 machine settings (these need to change so I put them as records instead of fields), these aren't the same line from line as some lines are made of different components. There's about 10 lines (though more may be added).

Each line runs between 10-60 different products (these change also) and each product runs at their own machine settings.

I am not entirelly sure how to go about doing this as if there are say 1 run with 200 settings (which is pretty much the lowest) and runs 10 products, thats already 2000 settings right there.

Any ideas?
Here's a picture of my relationships to see how the tables are set up
Here's my database, no information in it yet though

Am I on the right track of setting my database up? I am not entirely sure, I am still learning access and I have been reading up a lot on normalization but I never seen a example of a database that is doing what I am trying to achieve. How would you go about normalizing this project?

Thank you very much in advanced!!!

 

[The_Doc_Man]

You are letting numbers get in the way of design. Don't let the numbers overwhelm you. Who gives a flying fiddle-dee-dee that you will have 20 or 200 or 2000 settings? What you care about is that you have all similar items in appropriate tables. Normalization gets you started on that process.

I'm going to try to walk you through the process. Let's take two factors. You have a bunch of machines, each having a bunch of settings. The settings on those machines vary according to products being made on the machine assembly lines.

OK, basic entities from that paragraph are: Settings, Machines, and Products. Settings are associated with machines because that is where they are applied. Settings are also associated with products because the setting is different for different products. Right? So you need three tables (for this part of the problem.)

Table Machines - contains a machine identifier key that is unique for all machines that could have relevant settings. Has other data as needed for descriptive, location, or maintenance purposes.

Table Product - contains a product identifier key that is unique for all products you make. Has other data naming the product and factors thereof.

Table Settings - contains a Machine ID (as a foreign key or FK) corresponding to the machine on which that setting applies. Contains an FK to the product for which that setting applies. Contains the actual setting.

To find a list of settings by product, you build a query of machine tables JOIN with settings table using the machine ID as the joining field. THEN you just select the setting number you want from that query (as a second-layer query), group by the machine type code that is in your machine table, and there is your list of the required settings in an orderly fashion.

Now use that same type of logic to add FK's to the settings table and to add other fields as it seems appropriate. The process is merely the long, slow identification of entities that need to be in the table. By "entity" I mean specifically something that needs to be in your business model, tracked by the database because it is something you need to know about in some specific way.

 

[Joshin]

You are letting numbers get in the way of design. Don't let the numbers overwhelm you. Who gives a flying fiddle-dee-dee that you will have 20 or 200 or 2000 settings? What you care about is that you have all similar items in appropriate tables. Normalization gets you started on that process.

I'm going to try to walk you through the process. Let's take two factors. You have a bunch of machines, each having a bunch of settings. The settings on those machines vary according to products being made on the machine assembly lines.

OK, basic entities from that paragraph are: Settings, Machines, and Products. Settings are associated with machines because that is where they are applied. Settings are also associated with products because the setting is different for different products. Right? So you need three tables (for this part of the problem.)

Table Machines - contains a machine identifier key that is unique for all machines that could have relevant settings. Has other data as needed for descriptive, location, or maintenance purposes.

Table Product - contains a product identifier key that is unique for all products you make. Has other data naming the product and factors thereof.

Table Settings - contains a Machine ID (as a foreign key or FK) corresponding to the machine on which that setting applies. Contains an FK to the product for which that setting applies. Contains the actual setting.

To find a list of settings by product, you build a query of machine tables JOIN with settings table using the machine ID as the joining field. THEN you just select the setting number you want from that query (as a second-layer query), group by the machine type code that is in your machine table, and there is your list of the required settings in an orderly fashion.

Now use that same type of logic to add FK's to the settings table and to add other fields as it seems appropriate. The process is merely the long, slow identification of entities that need to be in the table. By "entity" I mean specifically something that needs to be in your business model, tracked by the database because it is something you need to know about in some specific way.

Hmm, I think I understand, though could you elaborate on the Settings table? So it would contain the Machine ID and have join to the machine ID from the machines table?

Something I am admittingly still extremely new to using is queries, I've relied completely on tables and vba for doing most everything for me (which mainly is the reason I also ran into a lot of normalization issues my first shot at doing this)

Thank you very much sir!

 

[The_Doc_Man]

OK, here is a shot at two questions.

Simpler concept first. Queries. Normalization is the process whereby you separate out unlike data items to keep each type of item in its own table. But there are legit reasons such as forms and reports that want to bring that stuff together. Here's the key thing to know. Forms and reports want a recordset. Tables provide a recordset. SO DO SELECT QUERIES. If you have a table-based recordset, you have a LOT of gyrations before you to combine elements from other tables. If you have a query-based recordset that involves a JOIN, and if the JOIN and table structures are correctly set up, you have just combined elements from two tables. If you need more than two tables, generally you have to layer your queries to JOIN the first query (with two tables already) to a third table, and so on. But when normalization is proper, you'll see exactly what you need to see.

Now let's tackle what these tables look like. I'm going to make up something. Let's say you want to remember two settings for one machine for each of two different products. Here is what the items might look like:

Table Machines
MACID = 1 (*), MACname = TRILITHIUM CRYSTAL DEMOGRIFIER, bldg = 31, floorgrid = 19
MACID = 2 (*), MACname = INTRANASAL LOBOTOMISER, bldg = 20, floorgrid = 6,
etc... and Prime Key = MACID

Table Controls
MACID = 1, CTLID = 1, CTLname=Amplitude
MACID = 1, CTLID = 2, CTLname=Phase
MACID = 2, CTLID = 1, name=Angle
MACID = 2, CTLID = 2, name=RPM
etc... and Prime Key = (compound) MACID and CTLID

Define a one:many relationship - one table, many controls. You can use various ways to reset the control ID for each machine. DCOUNT would tell you how many controls have been defined already for a given machine (based on unique MACID).

Table Products
PRDID = 1, PRDname = LEFT HANDED VEEBLEFETZER
PRDID = 2, PRDname = RIGHT HANDED VEEBLEFETZER
etc... and prime key = PRDID

Table Settings,
MACID = 1, PRDID = 1, CTLID=1, Value =30
MACID = 1, PRDID = 1, CTLID=2, Value = 60
MACID = 1, PRDID = 2, CTLID=1, Value = 45
MACID = 1, PRDID = 2, CTLID=2, Value = 90
Have indexes (non-unique) on each of MACID, PRDID, and CTLID.

Define two more relationships.

1. One product to many settings across PRDID.
2. One control to many settings across the combination of MACID and CTLID.

If you define the relationships first and THEN add the tables into the query builder, Access will automagically get the JOIN conditions right when you use the design grid to build the query.

Now you need to decide the order in which you want to see these things. Lets say you wanted to join the four tables together. Join the machine to the list of control names on the MACID. Call that query qMACCTLS. Join the list of Settings to qMACCTLS on the MACID and the CTLID. Call that query qMCSETTINGS. Now you are almost there. Join the product table to qMCSETTINGS on MACID, CTLID, and PRDID.

What you have now is a list of every machine, every control on that machine, every product, and every setting for which a machine and control are relevant to the product line. Order that outermost query or group it as you prefer to (say) bring all products together, then all machines, then all controls and their values. In a report, this would be a header by product, sub-header by machine, detail rows would be the control and value.

Because it is possible that you wouldn't use every machine to make every product, you only populate this table with RELEVANT settings. I.e. if you don't use the TRANSNASAL LOBOTOMISER to make left-handed veeblefetzers (PRDID=1), there will be no entry in the Settings table for PRDID 1. Which is actually kind of what you want - i.e. don't tell me about a machine that I don't use.

Now, what is the down-side? HERE is where you get daunted by size. You have to populate that settings table with all sorts of data. This might take a while because it is basically manual entry, unless you have a spreadsheet somewhere that was previously used to capture some of those values. If you have spreadsheets, you can import stuff from the spreadsheet into temporary tables, after which you will be able to do Append queries of an ad-hoc nature. This will not be trivial. But once it is done, it is done and what you have left should work pretty well.