A normalisation question (2 Viewers)

[EH_SG]

I created a database for my employer 2 years ago, initially to track component stock though it has broadened to encompass more.
It seemed best at the time to have a separate table for (1)the components (parts) that make up (2)sub-assemblies (sections) that make up (3)products. I now wish I had created 1 table that has all of the above in with a field(s) that define a category of parts/sections/products and 1 table that defines which items are required for other items. (I have attached a screen grab of my relationships to try and give a picture please be kind!)
I have a host of forms and reports that deal with each of the tables separately and sometimes brings a similar query for each table into 1 report eg overall stock value report.
I have wondered a few times if I should just suck it up and combine the 3 tables into 1, adjusting all the forms and reports as necessary but it has always felt like it would be a waste of time however I now wish to create a delivery note function and I am feeling the same pressure again as we do not only send out products but also sub-assemblies and components.
Does anyone have any wisdom for me? Should I bite the bullet and change it now or should I be using some form of union query to give the list of possible items to go onto the delivery note?

 

[mike60smart]

Difficult to make out your relationships from the screenshot.
Are you able to upload a copy of the database with no confidential data?

 

[mike60smart]

This is a back up of the tables (only) I created before I moved them to sharepoint so its not exactly like this now but the structure is the same. And this doesn't have all our customer contacts etc in.

Hi
Which are the table that you are thinking of modifying?

 

[EH_SG]

This is a back up of the tables (only) I created before I moved them to sharepoint so its not exactly like this now but the structure is the same. And this doesn't have all our customer contacts etc in.
(Repost without some numbers in!)

 

[EH_SG]

Hi
Which are the table that you are thinking of modifying?

Inventory, Sections and Products - each then has its own transactions table, which should maybe be 1 table also if I merge the initial three

 

[mike60smart]

Can you explain each of the 3 Transactions please.

 

[EH_SG]

These tables log each stock transaction for inventory, sections and products respectively

 

[mike60smart]

These tables log each stock transaction for inventory, sections and products respectively

Sorry but your tables and relationships do not make sense to me.
Points I have picked up are as follows:-
Numerous Lookup field in numerous tables.
You should not have any spaces in fieldnames or any non numerical characters ie !"£$%^&*(){}[]?><~@
Some of your Joins between tables do not have Referential Integrity enforced.

Some of the Guru's may give you further guidance.

 

[mike60smart]

I tidied up the main tables as shown in the attached screenshot

 

[mike60smart]

It would help if we could see the FE?

 

[The_Doc_Man]

In response to a verbal part of your question - ignoring the diagrams because mike50smart is looking at those - it has been discussed in several threads that you do better to call a sub-assembly a part. Then - presuming that all the parts of the sub-assemble are also individual parts in the main table - you can create a self-reference table (a.k.a. JUNCTION table) where you have an assembly part number (that refers to the assembly as a whole), a component part number (that refers to a part used to make the assembly), and the quantity of that component you need. There COULD be more in the junction table, but what I listed would count as a minimum. For multi-layered cases, you can have a component that is ALSO a sub-assembly. Things can get a little bizarre, but technically the junction table is the way to go.

In this forum, look up "assembly" or "component inventory" to see more.

 

[Ken Sheridan]

It seemed best at the time to have a separate table for (1)the components (parts) that make up (2)sub-assemblies (sections) that make up (3)products. I now wish I had created 1 table that has all of the above in with a field(s) that define a category of parts/sections/products and 1 table that defines which items are required for other items.

You can model products, assemblies (which might be sub-assemblies), and atomic base parts very simply with two tables. In one table each product, assembly and base part is a value in the same column in the table. The other table simply references two instances of the first table by referencing the key of the first table in a MajorPartNum or similar foreign key column, and in a MinorPartNum or similar foreign key column. A third column, Quantity, records the number of the minor part, which can be an assembly or base part, in the major part, which is an assembly or product of course. Other columns representing other attributes of the relationship can be included if necessary.

From this simple model a bill of materials for a product or assembly of arbitrary complexity can be generated at runtime. This requires recursive querying however, which unlike client server products like Oracle or SQL Server, Access does not support. It can be simulated however, and the attached zip file includes two apps which illustrate this. BoM.accdb builds a BoM to an arbitrary number of levels by progressively inserting data into a table in an external database created at runtime, whereas the simpler PartsTree.accdb does so to a fixed number of levels with a query which joins multiple instances of the tables.

 

[EH_SG]

I have tried to improve the relationships view to make it clearer how everything works together currently.
I do have junction tables (part to section and section to product)
@Ken Sheridan it sounds as though you are saying that if I had all the parts, sub-assemblies and products in one table then it is a more tricky process to get a list of all parts in one product? At the moment I achieve this by a query that incorporates both junction tables.

I know my database isn't perfect in lots of ways but its a question of could I/should I put the work in of having 1 table rather than 3 with just 1 junction table defining what makes up what? Will it be worth it not only to make the delivery note task much easier but anything else going forward eg an orders function and will it make a more efficient database?

 

[EH_SG]

Sorry but your tables and relationships do not make sense to me.
Points I have picked up are as follows:-
Numerous Lookup field in numerous tables.
You should not have any spaces in fieldnames or any non numerical characters ie !"£$%^&*(){}[]?><~@
Some of your Joins between tables do not have Referential Integrity enforced.

Some of the Guru's may give you further guidance.

Numerous Lookup field in numerous tables - why is this bad and what is the alternative?

Some of your Joins between tables do not have Referential Integrity enforced - I have no idea what this means in practical terms

 

[The_Doc_Man]

Some of your Joins between tables do not have Referential Integrity enforced - I have no idea what this means in practical terms

A JOIN query takes two tables and sorts/realigns them so that fields declared to have a common meaning show up together. For instance, your bank account number appears on your personal info page of your bank's web site and it ALSO appears on each check, deposit, withdrawal, interest posting, etc. because the transactions are RELATED to your account. Which is why we call it a RELATIONAL database. A JOIN is the SQL embodiment of one of those relations. RELATIONAL INTEGRITY is a property of a relation such that you don't allow the creation of "orphan" records such that a transaction account number for a personal transaction doesn't correspond to an existing personal record with that account number. I.e. there is a "broken" or "orphaned" transaction that can't be associated with anyone.

To enforce RELATIONAL INTEGRITY simply means that you will not allow creation of orphaned records. More technically, it means that if you have a one/many or many/one relationship, you are not permitted to make an entry in the MANY side of the relationship unless there is already a corresponding record in the ONE side of that relationship.

Numerous Lookup field in numerous tables - why is this bad and what is the alternative?

We need to agree on a definition. A LOOKUP FIELD is a field in which you include the possible values in the definition. But the way that Access implements this feature is to treat that list of values as a "hidden table" and you are looking up the entry in that hidden table. BUT lookup fields aren't usually designed to translate anything for you. They are more like validation tables. One problem comes in if you have multiple uses of the identical set of values in different places i.e. do the same lookup from two different tables. This would lead to duplication of data among many other ills.

The correct alternative is to have a separate and explicit table of values - one table for each of the things to be translated or looked up or whatever. One place to see the list of valid choices. Then you use JOIN queries for SQL to be able to look up, for example, that "AL" is the abbreviation of "ALABAMA" and "NY" is the abbreviation for "NEW YORK" etc. AND to be able to see the name spelled out completely in the query contents. With a lookup field, getting that translated text value is harder than it looks.

Search for "LOOKUP FIELD" in this forum - it is a topic that has been discussed many times. For beginners it seems like a great convenience - but once you start to do more with your DB, those pesky little lookup fields become more of a hindrance than a help.

 

[Ken Sheridan]

@Ken Sheridan it sounds as though you are saying that if I had all the parts, sub-assemblies and products in one table then it is a more tricky process to get a list of all parts in one product?

On the contrary. In my BoM.accdb file you simply select the product in the list box on the left side of the main form, and it's components are then shown in the subform on the right. In the simpler PartsTree.accdb file the quantities are shown rather differently. Whereas in BoM.accdb the total quantity of each base part or assembly which make up the product are returned, in PartsTree.accdb at each level of the tree the quantity of the parts or assemblies which make up the assemblies or parts in the next level up is shown in parentheses after each assembly or part name.

The tricky part is the simulation of recursive querying which BoM.accb uses, but the code in the demo does that, so you'd just need to transfer this to your own file. The background to this is that I started off by implementing one of the standard algorithms for BoM generation, but then wanted to return the aggregation of the base part weights per product. When I wrote the code to do this, I realised that the same code genearted the BoM itself, so I was able to ditch the original code and achieve both results with one set of code.

 

[Ken Sheridan]

Which is why we call it a RELATIONAL database.

Date has some interesting things to say on this:

Let {H} be a tuple heading and let t1, t2 …, tm be distinct tuples with heading {H}. The combination, r say, of {H} and the set of tuples {t1, t2 …, tm} is a relation value (or just a relation for short) over the attributes A1, A2, …, An, where A1, A2, …, An are the attributes in {H}. The heading of r is {H}; r has the same attributes, (and hence the same attribute names and types) and the same degree as the heading does. The body of r is the set of tuples {t1, t2 …, tm}. The value m is the cardinality of r.

……..I will at least explain why we call such things relations. Basically, each tuple in a relation represents an n-ary relationship, in the ordinary natural-language sense, among a set of n values (one value for each tuple attribute), and the full set of tuples in a given relation represents the full set of such relationships that happens to exist at some given time – and, mathematically speaking, that's a relation. Thus, the "explanation" often heard, to the effect that the relational model is so called because it lets us "relate one table to another" though accurate in a kind of secondary sense, really misses the basic point. The relational model is so called because it deals with certain abstractions that we can think of as "tables" but are known, formally, as relations in mathematics.

C J Date, 'Database in Depth – Relational Theory for Practitioners' 2005, p45 sq.

 

[The_Doc_Man]

Ken, I was considering the audience. I'm more pragmatic than theoretical when it comes to relational issues.

 

[Ken Sheridan]

I find the theoretical stuff fascinating. I greatly regret that I never learnt mathematics to a more advanced level, to be able to understand the basis of theory better. I have an inkling of what the relational algebra is all about, but I'm lost when it comes to the relational calculus.