1

I am working on a requirement where product needs a flexibility to add Lists of multiple number of columns. I am trying to fit data in data model with the help of key value pair.

For example -

I have a list Allergy which will have 3 columns Allergy, Reaction and Type Another list named Lens have only two columns Vendor Name, Cost

My model consists of following table

  1. LIST

It consist of LIST_ID and LIST_NAME

  1. LIST_ROW_MAP

It consists of LIST_ROW_ID, LIST_ID ( Foreign Key to LIST table) , ORDER ( to save row order)

3 LIST_ITEMS

It consists of LIST_ITEM_ID, LIST_ROW_ID (Foreign key to LIST_ROW_MAP table ) , KEY , VALUE

(Sorry could not post picture as I do not have privilege yet)

Though I can save the data, for retrieving I am facing challenges. I tried query marked as answer in below link but it can help when name of columns are known and when it is for single table stored as key value.

Transposing a Key Value pair SQL table

I am trying to store multiple table's data so there should be something dynamic. Please help :)

Community
  • 1
  • 1
ADK
  • 11
  • 2
  • Does the key/value pair content need to be filterable? Do you need to be able to write SQL queries against the content to find specific records? – Rob Epstein Aug 01 '14 at 09:42
  • Yes. I have to show List and their particular LIST_ITEMS on front end – ADK Aug 01 '14 at 09:44
  • Does it only need to be filterable in code or also via SQL? Sorry for the continued clarification questions. – Rob Epstein Aug 01 '14 at 09:45
  • Actually we do everything through stored procedure. So via SQL is the correct answer for this question – ADK Aug 01 '14 at 09:46
  • Brilliant. I realize the post is tagged as SQL. Just wanted to make sure. – Rob Epstein Aug 01 '14 at 09:48

0 Answers0