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 :)

Improve this question
5
  • 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? Commented Aug 1, 2014 at 9:42
  • Yes. I have to show List and their particular LIST_ITEMS on front end Commented Aug 1, 2014 at 9:44
  • Does it only need to be filterable in code or also via SQL? Sorry for the continued clarification questions. Commented Aug 1, 2014 at 9:45
  • Actually we do everything through stored procedure. So via SQL is the correct answer for this question Commented Aug 1, 2014 at 9:46
  • Brilliant. I realize the post is tagged as SQL. Just wanted to make sure. Commented Aug 1, 2014 at 9:48
Load 7 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.