How do create table with dynamic no. of columns for data entry

schema, sql, sql-server

Solution

I suggest a standard many-to-many relationship using a middle table. So you would use 3 tables:

- StoresTable: the list of stores

- ItemsTable: the list of items

- StoreItemsTable: a list of items-in-stores, each row will have a foreign key to both the stores table and the items table

Hope that helps.

Problem

I'm currently working on a project where I need to save data depending upon the active no. of items. Its like.. Stores can have n no of items which can be dynamically added or reduced from admin back-end. Each item can either be activated or deactivated. NO problem. But I need to manage/save these dynamic n no. of items in a table for each store. I'm not able to decide on the table schema. Any help will be highly appreciated.

Original source