Relationship Suggestion

[panchristo]

Hi all once again!

Can someone suggest possible solutions to the following:

I need to store positions for items.

Since I can't yet manage Infinite Hierarchies (No FM Magazine Subscription), I've set up a fixed structure with six levels of position denoting fields (i.e. Area-Building-Row-Column-Shelf-Box) all of which receive the appropriate Value list Items through a dedicated "Registered Positions Table" with appropriate relationships. So far so good.

An item is usually stored in a box (uniquely identified by name), BUT may as well be stored by itself on a shelf.

What I would like to do is to manage the position of the item in case it is stored inside a box both from its record as well as from the box's record.

Meaning: to be able to change from a value list the box it is stored into (easy-done already), but also to be able to change the position of a specific box and change the contained items' positions automatically.

Basically, it would be something like:

IF the item has a position with box-level specified, update automatically the Area-Building-Row-Column-Shelf portion from the box's position, OTHERWISE change position manually from item's record.

Do you have any ideas?

I know I would like to have a hierarchical portal with tree view and select in one move what I want but unless someone makes the structure for me I can't do it myself.

P.S. According to suggestions I will provide additional restrictions, to keep the length of posts reasonable.

Storage.zip

[comment]

I don't see anything "infinite" in your description. IIUC, the only two things that can move is item and box. The problem of items stored directly on the shelf could be solved by designating a "Box 0" for each shelf. So an item can only move from box to box, and a box can only move from shelf to shelf.

An item needs to store only its parent box ID (unique among ALL boxes) and a box needs to store only its parent shelf ID (unique among ALL shelves). All the other information can come from the Shelves tables, and/or from the tables further up in the hierarchy - if these tables are necessary at all.

[panchristo]

Thanks for your prompt answer.

To answerbak - if I understand correctly:

Starting from the desired fuction of a hierarchical portal, I came up with the six field fixed structure, to let users update the registered positions easily, to select gradually the desired position, because the total number of positions will be large. Therefore a way of limiting those records in order to get to the one you need is essential for me. From the prospective users standpoint also, it would be desirable to create boxes-for-positions and positions-for-boxes in a separate GUI way.

As for the infinite of the hierarchy, it refers to the unpredictable arrangement of a storage room. My recipe will sooner or later will need modifications, whereas a tree structure where a user can arbitrarily modify it in several ways would be better in my opinion.

[comment]

Starting from the desired fuction of a hierarchical portal

I don't think that's a good starting point. IMHO, the data model should first and foremost reflect the real situation. If additional relationships are required to support a desired user interface, they can be added later.

[panchristo]

From what I have seen from FM Magazine's "Infinite Hierarchies" such a portal is compatible with my proposed structure isn't it?

[comment]

I don't know - I am not sure I understand your proposed structure that well. I think an item goes into a box (or a shelf), so that would be a one-to-many relationship (one box/shelf has many items). I don't see this in your file.

I believe you have two basic choices here: either a rigid hierarchy, e.g.

Areas -< Buildings -< Rows -< Columns -< Shelves -< Boxes -< Items

or a flexible (recursive) one, where:

Locations -< Items

with the locations records self-joined in any arbitrary hierarchy.

The second options is more flexible (for example, an item could be assigned directly to a building), but more difficult to implement.

In either case, Items are on the child side of the relationship, therefore an item should store its parent's ID - and only its immediate parent's ID.

[panchristo]

Although I can't implement the recursive structure myself, I understand it fits better my concept.

To explain myself, it would be used to store the registered/approved storage positions for items and then, projected through a portal it would allow to select directly the item's parent (=its storage position) as you said.

I'm desperately trying to teach myself recursive structures, but it seems I am still far away from implementing them; seems to be what i need though.

Sorry for thinking in reverse-engineering, I am a user firstmost(not sure if it is bad).

[comment]

Although I can't implement the recursive structure myself, I understand it fits better my concept.

Are you sure about this? Do shelves move freely from building to building? Can a column belong directly to an area (without having a row)? Is it possible for a box to have several buildings? These are the types of situations that a recursive structure is designed to handle.

Anyway, a recursive structure is not that difficult; basically, all you need is two self-joins:

ParentLocation -< Locations -< ChildLocations

and then of course:

Locations -< Items

BTW, rigid or flexible, I don't think value lists will be the best selection method here.

[panchristo]

BTW, rigid or flexible, I don't think value lists will be the best selection method here.

What would you suggest then?

[comment]

Perhaps something along the lines of:

http://fmforums.com/forum/showpost.php?post/334277/

[panchristo]

Thanks a lot Comment, your help seems to put me in the right direction. I guess I have to research on that and come back again for more questions :)