CODEWITHSUNDEEP
sqlsql-serverdatabaseormkey-value
disasterkid
disasterkid

Reputation: 7278

Having all contact information in one table vs. using key-value tables

(NB. The question is not a duplicate for this, since I am dealing with an ORM system)

I have a table in my database to store all Contacts information. Some of the columns for each contact is fixed (e.g. Id, InsertDate and UpdateDate). In my program I would like to give user the option to add or remove properties for each contact. enter image description here

Now there are of course two alternatives here:

  1. First is to save it all in one table and add and remove entire columns when user needs to;
  2. Create a key-value table to save each property alongside its type and connect the record to user's id.

These alternatives are both doable. But I am wondering which one is better in terms of speed? In the program it will be a very common thing for the user to view the entire Contact list to check for updates. Plus, I am using an ORM framework (Microsoft's Entity Framework) to deal with database queries. So if the user is to add and remove columns from a table all the time, it will be a difficult task to map them to my program. But again, if alternative (1) is a significantly better option than (2), then I can reconsider the key-value option.

Upvotes: 2

Views: 309

Answers (1)

Eric The Red
Eric The Red

Reputation: 15

I have actually done both of these.

Example #1

Large, wide table with columns of data holding names, phone, address and lots of small integer values of information that tracked details of the clients.

Example #2

Many different tables separating out all of the Character Varying data fields, the small integer values etc.

Example #1 was a lot faster to code for but in terms of performance, it got pretty slow once the table filled with records. 5000 wasn't a problem. When it reached 50,000 there was a noticeable performance degradation.

Example #2 was built later in my coding experience and was built to resolve the issues found in Example #1. While it took more to get the records I was after (LEFT JOIN this and UNION that) it was MUCH faster as you could ultimately pick and choose EXACTLY what the client was after without having to search a massive wide table full of data that was not all being requested.

I would recommend Example #2 to fit your #2 in the question.

And your USER specified columns for their data set could be stored in a table just to their own (depending on how many you have I suppose) which would allow you to draw on the table specific to that USER, which would also give you unlimited ability to remove and add columns to suit that particular setup.

You could then also have another table which kept track of the custom columns in the custom column table, which would give you the ability to "recover" columns later, as in "Do you want to add this to your current column choices or to one of these columns you have deleted in the past".

Upvotes: 1

Related Questions