1

I have two entities that describe a Project and an Item (product). When an item is added to a project, I create a relationship entity ProjectItem.

Separate from that I have a Space entity (like a room) and a relationship between a Project and Space as in ProjectSpace.

In my application there is a flow that describes assigning a specific quantity of an item to a space, e.g. adding 10 chairs to a room. Here is where I'm not sure how to design this association table.

The front-end can simply request that an item_id, project_id and space_id be associated with a quantity of 10. The back-end can check that there is a relationship record project_item (for project_id and item_id) and a valid project_space record (for project_id and space_id) to validate the entry.

Now I wonder whether my Assignments table should be constructed as a relationship between:

  1. project_item_id with project_space_id with quantity
  2. project_id with item_id with space_id with quantity
  3. project_item_id with space_id with quantity

The question is about flattening relationships vs nesting relationships in a table design.

EDIT The flow goes like this:

  • An item gets selected for a project (like adding it to a pool of items for this project), hence project_item
  • Spaces have already been associated with projects, hence project_space
  • Only items that have been added to a project can be added to spaces, and only to spaces that belong to the same project
Stefan Zhelyazkov
  • 2,599
  • 4
  • 16
  • 41
  • [relationships between 3 entities in ER diagram--is a ternary enough or are 2 binaries also needed?](https://stackoverflow.com/a/45557994/3404097) – philipxy Aug 08 '19 at 21:51
  • @philipxy the 2 binary relationships `project_item` and `project_space` are needed. Now here is where I don't know what the best way to design the relationship table comes. My initial thoughts are to create another binary relationship between `project_item` and `project_space` and place a `quantity` field on it. However, I'm also thinking about flattening the relationship with a ternary relationship between `project`, `item` and `space`, with a quantity on it. Looking for advice from experts who've done this before. – Stefan Zhelyazkov Aug 09 '19 at 16:06

1 Answers1

0

Do you genuinely have a ProjectItem relationship? Are the 10 chairs really dedicated to the project or to the space? For example, if you have 10 chairs in the room for one project, and not to another, but the other project can use that room, are the 10 chairs not available to that project? If you change rooms for your project, do the chairs automatically follow? It's unlikely, physical objects tend not to follow logical entities. They can but they don't tend to..

Personally, I'd stick with a project_space and space_item (with quantity). Then different spaces can contain different items, and they can by transitive association be allocated to projects.

If items truly can be allocated to a project with them existing in space (neat trick that!), then you can keep the project_item association with its own quantity value on the assignments table, but personally, I'd still create a "virtual" space which would contain the items.

Rob Conklin
  • 8,806
  • 1
  • 19
  • 23
  • Thanks Rob! I expanded my question with important details that I realized I missed. For example, when an item gets added to a project, it creates a pool of items that are available within the project (hence `project_item`). Spaces have already been associated with a project, hence `project_space`. An assignment can only happen for an item added to the project and can only be added to a space that also belongs to the project (each space is locked to only 1 project). Would that change your schema? – Stefan Zhelyazkov Aug 08 '19 at 15:20
  • No, that actually reinforces my point. if a space is fully dedicated to a project, all the items are allocated to those spaces, and thus rolled up to that project. I think you still have a strict hierarchy of item->space->project. I could very well be wrong, and if so you could very well need both relationships, item->space and item->project, but it complicates things rather a lot, especially as things belong in both spaces and projects, and I assume they are finite and have real-world identities. – Rob Conklin Aug 12 '19 at 22:59