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

Question

Hi

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.

Answer 1

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.

Answer 2

Hi,

your problem is actually not that hard if you use a different approach.

A store can have a number of items. So basically Susi's store has 1 item, but suddenly she wants 2, and you would like to add a column. This is very difficult if she suddenly wants to add 2000 items.

The best approach would be to use a store table (which has the name of the store, the date it was created and a primary key) and a items table. You can then add items as entries to the table and link them to the store via the stores primary key.

An example:

Store table:
PK    Name             Owner
1     Sunshine Store   Susi
2     Moonstore        Harald

Item table:
PK    Name     Store_id        Price
1     Candle   1                2.44
2     Table    1               51.44
3     Chair    2                6.55

This allows you to add as many items to any store you want. The Store_id is called a Foreign Key in this example, because it links the items to the store. You can then use SQL commands to select the items, e.g.

 "Select * from ITEMS where Store_id = 1;"

and get all of Susi's items as an answer.

Good luck!

Answer 3

i think you use active column and use that active bit for every purpose because that's very good for future in other transaction too.