0

I'm trying to design a database architecture for a simple inventory program.

This inventory will consist of items in boxes. Boxes contain items, and are items themselves.

I imagine box will be a super type of item.

My main hangup is how to structure/constrain the relation between boxes so that I can still constrain the unique condition that an item can only be in one "place" at a time.

I know that many-to-many relations should let me know which items are in which boxes, but what about boxes that are in other boxes?

For instance:
Box A contains Item B and Box C. Box C contains Item D.

How can I design a system that when I look for Item D I will find it inside Box A and Box C (really great if I could also know how the relation of the boxes -- latter is inside the former).

Rick James
  • 135,179
  • 13
  • 127
  • 222
zephyrus
  • 1,266
  • 1
  • 12
  • 29
  • Are you ok to design considering box as item ? In that case you can use Item(PK,FKParent,Type), where FKParent will be constraint to PK. and you can specify Type either Box or Item. – Maharajan Sep 10 '18 at 02:32
  • 1
    Your statement " so that I can still constrain the unique condition that an item can only be in one "place" at a time." seems contradictory to the example How can I design a system that when I look for Item D I will find it inside Box A and Box C. Can you provide more details clearly please.. – codeLover Sep 10 '18 at 05:06
  • @codeLover apologies for any confusion. When I wrote one "place" I meant that an item cannot be in multiple boxes ~unless~ one of those boxes is inside another. In the example I gave, `Item D` is inside both `Box A` and `Box C` but this is allowed since `Box C` is inside `Box A`. Does that clarify? – zephyrus Sep 11 '18 at 19:13

1 Answers1

0

You can use the self relationship with the table itself. You can mention below mentioned columns in your database design:

ItemId(PK)
ItemName
ItemType(Can be B for Box and I for item)
ItemContainer(Will contain ItemId from this table itself and will be null if it is not present in any box, if required add check constraint that if itemType is I then it cannot be null)

You can place the data as:

ItemId      ItemName      ItemType     ItemContainer
  1         BoxA             B            (null)
  2         BoxB             B               1
  3         ItemA            I               2

Since ItemId is a primary key, thus, database will allow only one row per item and thus, it will be available only in one box.

Check if this design helps...

codeLover
  • 2,571
  • 1
  • 11
  • 27