equipment / components database

[PeterDS]

I am currently creating an orders database for equipment that is sent to customers for them to build themselves.

 

I have created 6 tables: Sites (customer site info),  Orders , OrderLine, Assembly, AssemblyLine & Components.

 

Customers order the equipment (the assemblies) & the assemblies are made up of a number of components. The equipment/assemblies can be delivered over a number of dates - hence the Phase No/Delivery date in orderline.

 

 

What I want to do is to provide a report to our transport people which lists all the equipment broken down into components by delivery date/phase.

I hav attached my file which will hopefully mae things a littl clearer. The full database will have more fields but before I add them I want to make sure that the structure I have created can do what I need it to, and I am struggling at creating the report which displays the equipment & the component parts that the equipment is made up of.

 

As a simplified example:

 

Delivery Date: 01/02/2014

 

Assembly ID: 0001    Assembly Name: Large Gubbins

Component 1 ID: 9015     Component1: Shelf                Number of components: 5

Component 1 ID: 9135    Component2: Bracket (Lg)      Number of components: 3

Component 1 ID: 9012    Component3: Screws              Number of components: 10

 

Assembly ID:0022     Assembly Name: Small Gubbins

Component 1 ID: 9015    Component1: Shelf                   Number of components: 3

Component 1 ID: 9020    Component2: Bracket (Sml)      Number of components: 2

Component 1 ID: 9012    Component3: Screws                Number of components: 6

 

 

What I would like to know is, can such a structure deliver such a report, or do I need to go back to the drawing board & re-structure my database?

 

 

 

 

Assemblies.zip

[Matthew F]

 

 

 I am struggling at creating the report which displays the equipment & the component parts that the equipment is made up of.

 

On your relationship graph each Orderline represents one Assembly.  Each Assembly can have more than one Component so you're going to have trouble displaying all these components in the Orderline portal. 

 

Consider making your report using an OrderLine layout.  On this layout place related fields from Assembly or add a portal to AssemblyLine.   

 

-- edit --

 

Additional commentary on your database design:  It may not be ideal to connect all your tables together sequentially like link sausages. (Though its not necessarily wrong either) For example, you are not going to want to reach all the way from the Sites table over to the Components table because it would probably not deliver what you expect.  As you know, if you place a portal from a related table on a layout, it will display all of the records that meet the match criteria (e.g. Orders on a Sites layout).   However, if you place a portal on a layout representing a table that is connected via an intermediary table (e.g. Orderline on a Sites layout), then it will display all of the records that are related only to the first matching record in the intermediary table (i.e. the Orderlines for the first matching Order, not all of the Orderlines for that Site). 

 

That being said, I still think that my recommendation, above, should work even though I said that you should put the AssemblyLine portal on an OrderLine layout, and they are linked via an intervening (Assembly) table.  The reason that I think that this should work is that there should be only one matching Assembly record for each Orderline record, so it is fine to show the AssembyLines related to that Assembly. 

 

There's also nothing stopping you from creating a 2nd table occurrences for any given table in order for it to create new relationships, they don't need to be all connected together in a giant sausage or spider web.    

Edited January 9, 2014 by Matthew F