How to create nested tables in SQL Server

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.

Answer

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.

Advertisement

Answer