Key value pairs in relational database

by

Before you continue on your approach, I would humbly suggest you step back and consider if you really want to store this data in a “Key-Value Pair”table. I don’t know your application but my experience has shown that every time I have done what you are doing, later on I wish I had created a color table, a fabric table and a size table.

Think about referential integrity constraints, if you take the key-value pair approach, the database can’t tell you when you are trying to store a color id in a size field

Think about the performance benefits of joining on a table with 10 values versus a generic value that may have thousands of values across multiple domains. How useful is an index on Key Value really going to be?

Usually the reasoning behind doing what you are doing is because the domains need to be “user definable”. If that is the case then even I am not going to push you towards creating tables on the fly (although that is a feasible approach).

However, if your reasoning is because you think it will be easier to manage than multiple tables, or because you are envisioning a maintenance user interface that is generic for all domains, then stop and think really hard before you continue.

More Related Contents:

  1. SQL Selecting from three tables
  2. Which is faster/best? SELECT * or SELECT column1, colum2, column3, etc
  3. How to implement a many-to-many relationship in PostgreSQL?
  4. Entity Attribute Value Database vs. strict Relational Model Ecommerce
  5. How can I schedule a job to run a SQL query daily?
  6. Reset identity seed after deleting records in SQL Server
  7. What is the difference between JOIN and UNION?
  8. What is atomicity in dbms
  9. SQLite – How do you join tables from different databases?
  10. MySQL dump by query
  11. SQL ROWNUM how to return rows between a specific range
  12. How would you implement sequences in Microsoft SQL Server?
  13. SQL how to compare two tables for same data content?
  14. What does =* mean?
  15. Does MS Access support “CASE WHEN” clause if connect with ODBC?
  16. MySQL equivalent of DECODE function in Oracle
  17. What datatype to use when storing latitude and longitude data in SQL databases? [duplicate]
  18. Why do NULL values come first when ordering DESC in a PostgreSQL query?
  19. Favourite performance tuning tricks [closed]
  20. Is it necessary to create tables each time you connect the derby database?
  21. Venn Diagram for Natural Join
  22. Database efficiency – table per user vs. table of users
  23. SQL ON DELETE CASCADE, Which Way Does the Deletion Occur?
  24. Find rows that have the same value on a column in MySQL
  25. what is the difference between GROUP BY and ORDER BY in sql
  26. What does ON [PRIMARY] mean?
  27. How do you interpret a query’s explain plan?
  28. Oracle DB: How can I write query ignoring case?
  29. PostgreSQL nested INSERTs / WITHs for foreign key insertions
  30. SQL Server equivalent of WM_CONCAT function [duplicate]

Leave a Comment