CODEWITHSUNDEEP
sqlsql-server
Tonyb
Tonyb

Reputation: 1

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.

Upvotes: 0

Views: 103

Answers (1)

Mike67
Mike67

Reputation: 11342

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.

Upvotes: 1

Related Questions