Item measurements display through 2 tables

[TysonB]

I'm revising a database that stores information about pieces within an art collection. We store information about the dimensions of each piece within the collection. Each piece within the collection can consist of one or more components (up to 3 components per piece) and I need to collect dimension information for each component so that the space requirements for the WHOLE piece can be estimated while at the same time it can report on the individual dimensions of each component. Each component, depending on the piece, can have 2 or 3 different dimensions of 4 different types (height, width, depth, thickness). The way it is currently done is by having a Piece table and a related Dimensions table, and within the Dimensions table there are fields for each possible dimension a component might have. This does NOT enable them to pull the reports they want. What they want is a breakdown showing ONLY the exact dimensions for each component of a piece, which they would like to put onto a label for their display setup staff.

The following table structure was proposed by one of the other (non-Filemaker) database people I work with:

Piece table

piece_key - Primary key, unique ID for the piece as a whole

Component table

piece_key - foreign key back to Piece table

component_key - primary key, unique ID for the component

component_type - text field clients fill in

Dimension Table

component_key - foreign key back to Component table

Dimension - the actual numerical measurement

Dimension_type - whether it is height, depth, thickness, width

Measurement unit - inches, centimetres, whatever... Might dispense with this one, as we'd standardize on inches. However, down the road they might want to start tracking weight.

This structure makes complete sense to me, and allows us to track any number of dimensions for any number of components..... However....

What I do NOT know how to do is to be able to display at the Piece level all of the possible components WITH all their relevant dimensions. Since I cannot place a portal within a portal I cannot have something like

Piece - Frame - height 20

- width 25

- Thickness 1

- Support - height 45

- width 25

Ideally I'd like to be able to display something like

Piece - Frame - height 20 by width 25, Thickness 1

Support - height 45 by width 25

If all the dimensions were in the same record (like it was in the older Dimensions table) I could concat them together into one field. However, since they exist as individual records within the Dimensions table, I cannot figure out how to put them together without using a portal within a portal.

If anyone has any suggestions I would really appreciate them.

[Fitch]

If I understand your description, each component of a given piece can have only a single height, width, etc. Therefore what is the value in putting the dimensions in a separate table? In database terms: the dimension seems to be an attribute of the component, rather than its own entity.

And how can the components have different dimension attributes? Doesn't everything have 3 dimensions? Why does the support have no thickness? Isn't depth the same as thickness?

If you would clarify for me, I think we can resolve this.

[comment]

If all the dimensions were in the same record ... I could concat them together into one field

You can do it with the proposed structure, too. It would be easier if you had the Advanced version, or at least version 8.5 - still, if you define a calculation field, say cRow, in the Dimensions table as =

Type & ": " & Dimension & " " Unit

you can then have another calculation field in Components along the lines of:

GetNthRecord ( Dimensions::cRow ; 1 ) & ", " &

GetNthRecord ( Dimensions::cRow ; 2 ) & ", " &

Case ( not IsEmpty ( GetNthRecord ( Dimensions::cRow ; 3 ) ) ; GetNthRecord ( Dimensions::cRow ; 3 ) )

to display the measurements as a comma-separated array.

Or you could have a repeating calculation field using GetNthRecord() in a similar fashion to get each measurement into a specific repetition.

BTW, I am not at all convinced you need a separate table for the dimensions, but then I don't understand the 'dimension type' thing: can't your measurements conform to the standard trio of length (horizontal), height (vertical) and depth (thickness)?

[TysonB]

What makes this whole dimension recording thing complicated is that different types of components DO have different kinds of dimensions that get measured. For example, the base for a sculpture in their current setup MAY have a diameter measurement and a height measurement, whereas a frame for a picture may have length X height but ALSO a thickness so that they know how far out from the wall the frame extends (don't ask ME why they need to know this, I'm just the DB guy). Anyway, I will investigate the array suggestion proposed by the next reply, but if you've got any other thoughts I'd appreciate them. I'm kinda casting around wildly about this one....

[TysonB]

This WOULD be much easier if they were only collecting Length, Width and height, but they aren't. They collect thickness, for the thickness of a picture frame, they collect diameter, for the diameter of the base of a sculpture, they've talked about collecting weight for larger pieces. Either way, I'm going to give your array suggestion a shot when I get a chance. Haven't tried them at all, so this should be interesting.

[Fenton]

Yeah, I'd have to agree with comment, that it would be easier if the various dimension fields were in the Component table. While I can see that a separate Dimensions table is good relational design, it is one of those cases where it is more trouble with little benefit (that I can see).

Another idea would be to figure out what kind of component it is. There can't be that many different combinations of dimensions. It seems that having a type of "diameter" would mean a certain set of dimensions. In which case you'd know what combination of labels & dimensions to use.

You could use a List ( Dimensions::Type ) calculation to get a list. Then use PatternCount ( the List; "diameter") to see if the component had it.

To be able to pull out which dimension you wanted, no matter what order it was entered, there would be two approaches. The first would be to sort the relationship. Then assume it would have a known set of dimensions, and use GetNthRecord() to pull each out. That would assume that they were all there.

Or you could add unstored calculation fields (result Text) to Components, such as:

z_cDiameter_txt = "diameter"

Use that in a relationship: z_cDiameter_txt=::Dimensions::Type

would get you the diameter.

Etc. for each Type.

You could even put the related fields, based on the above relationships, on the Component layout, labeled with their type. Which would give the same interface as putting them in the Component table to start with ( :-)

[Colin Keefe]

I'm revising a database that stores information about pieces within an art collection. We store information about the dimensions of each piece within the collection. Each piece within the collection can consist of one or more components (up to 3 components per piece) and I need to collect dimension information for each component so that the space requirements for the WHOLE piece can be estimated while at the same time it can report on the individual dimensions of each component.

I think you're better off keeping 3 sets of dimensions for each component in the component table, and for the parent artwork table, give it its own set of dimensions, to be free form entered.

Given what art can be, I just don't see how you're going to mathematically model it by adding up component dims.

Example:

Artwork A consists of:

Component A: A bucket of glue, dims 12 " dia x 14" high

Component B: A kilim rug, 48" x 112"

Component C: A laptop computer in a suspension frame, hung at variable distance from ceiling.

Given the that bucket of glue is under the laptop, which is over the corner of the rug ONLY when the particular installation of the piece is put in an entryway (the artist specified a different configuration when put in a corner)...well, you get the idea.

An extreme example, but it holds true when you consider that the positioning of components in relationship to each other in three dimensional space is very difficult to represent in database form.

Is a triptych always hung in a horizontal sequence? Can be arranged vertically? In an L configuration? Diagonally?

Does the main component ALWAYS rest on a pedestal? Can it rest on the ground?

Are any of the components ever displayed separately? Not so common for contemporary artwork, but for archeological artifacts, quite common.

[Fitch]

I like Michael's (comment's) suggestion, if you're going to keep the dimensions in a separate table. (BTW, the List function Fenton mentioned requires at least FM 8.5).

Another option: if you kept the dimensions in the component table, you could use a calc along the lines of:

Case( 

    not IsEmpty( height ) ; "height " & height & ", " & 

    not IsEmpty( width ) ; "width " & width & ", " & 

    not IsEmpty( thickness ) ; "thickness " & thickness & ", " & 

 etc...

    )

I wouldn't recommend this if there were hundreds or even dozens of possible dimensions, but since I imagine there are fewer than 10, this is a feasible method.

[comment]

There's no shortage of alternatives here - another one that comes to mind is to make the portal into the Dimensions table itself, with calculated labels for components appearing only on the first row of each group:

http://www.fmforums.com/forum/showpost.php?post/266405/