How to create nested tables in SQL Server

Question

I don't understand how to create nested tables. My project is about beer. I have different coolers for sale, and each has a capacity for n number of beers. Each cooler comes preloaded with beers, and for each slot of beer of a cooler, the buyer can select which beer is in the slot. So, I have a table for all my coolers, and table for all the beer types that can be placed in each slot.

I can make an in-between table called beer-slots with entries slot_id (PK) and beer name (FK). But I do not have a way to reference the cooler slots in the cooler table to the ID of each slot in the beer-slots table. I could add 24 columns for a 24 slot cooler, but each cooler has n number of slots, therefore I need a variable number of columns using this method. I also though of making a column called Slot_ID in the coolers table with comma separated columns, but that is stupid.

I feel like I am missing something obvious. It feels like I need 3d rather than 2d.

Mike67 · Accepted Answer · 2020-08-02 00:32:36Z

1

You're correct, 3 tables are needed:

Cooler:
   CoolerID
   CoolerName
   TotalSlots   -- max capacity, some slots may be empty

CoolerBeer:    -- this connects the other tables
   CoolerID
   CoolerSlot   -- this is the slot number in the cooler
   BeerID

Beer:
   BeerID
   BeerName

You can take it a step further and use 4 tables (Cooler \ CoolerSlot \ SlotBeer \ Beer) but that may be overkill for this project.

edited Aug 2, 2020 at 0:32

answered Aug 2, 2020 at 0:25

Mike67

11.3k2 gold badges9 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to create nested tables in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related