What is a good schema to represent keystones in 3 space?

[Danxyz]

I'm trying to figure out the best way to structure a database to hold a list of Keystones (a six sided object made up for 4 sides that are squares, and 2 sides that are trapezoids).   I need an object Polygon representing each side of the Keystone (because I will render these Keystones in 3-space and won't always render all Polygons depending on the view).  Each Polygon shares its 4 corners with neighboring Polygons.  And each of the 8 (x,y,z) Points defining the Keystone map to 3 different Polygons (every corner of the Keystone is the meeting point of three Polygon sides).  

There is a 1:many relationship with Keystones:Polygons.  No problem.  One table of Polygons with a field Keystone_ID that relates to the Keystone table and we're good.

But I'm confused about how to relate a table of Polygons to a table of Points.  One Polygon maps to four Points.  And one Point maps to three Polygons.   There must be a simple solution but it eludes me.

thank you for your ideas..

[comment]

9 hours ago, Danxyz said:

There must be a simple solution

I am not sure of that. Much depends on your intended workflow.

Strictly speaking, the relationship between Polygons and Points is many-to-many -so there should be a join table in-between. However, if you don't need to record anything specific about a join, you could settle for a repeating field with 4 repetitions in the Polygons table to store the related points keys. This is assuming you would first create all the points of a keystone, and only then add them to each polygon.

Another option is to enter the points and the polygons in some prearranged order, so that, for example, the polygon at position #5 always maps to points in positions 1, 4, 5 and 6.

 

[Danxyz]

Thank you.  I've not used a join table before but I get the idea and think this will work.

On your second option, I can indeed manipulate the order of record creation for any of this.  And then store an Anchor point for each Polygon and reference the remaining points with a recipe as you suggest.  But I've always avoided addressing records as record n, record n-y, n+z, etc.. because I felt that was error prone as it assumes a very definite order in the foundset.  Also, making it possibly very slow as we are not using the relational power of the database.  Perhaps that worry is unfounded.  Or maybe I'm missing your idea on this one.

[comment]

As I said, much depends on how you intend to work with this and what do you hope to get as the product.

Personally, I would be inclined - at least initially - to put database design rules aside and go more with the geometry. That means that every keystone object gets 8 vertices labeled A, B, C, D, E, F, G and H and then there are 6 faces ABCD, ABFE, BCGF, DCGH, ADHE and EFGH. These faces are given by the vertices, so unless you have something specific to say about each face, I don't see why you would even need to create a record for each. Even if you do, it might be much more convenient to use repeating fields here: 3x8 for the vertices coordinates, and nx6 for any attributes of the faces. However, this may limit your reporting capability so - again - the crucial question is what is the ultimate purpose of this exercise.

 

[Danxyz]

I am creating static renderings of a Segmented Wood Bowl using SVG.  All Polygons in a given Keystone are the same color/texture.  But I need to determine the distance of each Polygon to the Viewer's Eye in order to decide what order to render.  Also, I need to identify which side of a Keystone is which Polygon as I can make some simplifications on rendering based on this (e.g. top view, only render top Polygons).  Hope that answer your question.

I don't envision any heavy reporting here.  but I will be cycling through the data to compute the rendering and generate the html.  Speed here will matter some.

Thank you for all the help.  You really got me un-stuck.

[Danxyz]

Having thought about it, I'm going with your last suggestion.  One table for Keystones.  8 vertice fields ... each a repeating field for the x,y,z coordinates.  The Polygons will be constant recipe for each keystone so no need for that to be in the database.  Simple.  Should be fast too.

 

Thank you!

[comment]

22 minutes ago, Danxyz said:

8 vertice fields ... each a repeating field for the x,y,z coordinates. 

Actually I meant 3 fields (x, y, z) with 8 repetitions each. But you need to ask yourself what will you search for (if at all) and what would you expect to find.